개발자를 위한 인덱스 생성과 SQL작성 노하우 : 9-11

 

- 공정쿼리 작성법

 

공정쿼리란, 무엇을 어떻게 조회할지에 대한 내용을 담고 있고, 쿼리 자체만으로 인덱스 생성 위치와 실행계획을 알 수 있는 쿼리이다.

 

무엇을 어떻게 조회할지에 대한 내용을 모두 포함해서 쿼리를 작성하므로 공정쿼리로 작성한 쿼리에서는 쿼리의 결과뿐만 아니라 생성해야 할 인덱스 정보와 접근돼야 할 실행계획 정보까지 모두 알 수 있다.

 

오라클 CBO(Cost Based Optimizer) 방식은 통계정보를 이용해 비용을 계산한 후 SQL을 수행한다. 통계정보가 존재하고 비교적 정확하다면, 오라클은 대부분의 쿼리에서 올바르고 적절한 실행계획을 제공하고 쿼리를 수행한다.

 

만약 통계정보가 존재하지 않거나 잘못된 정보를 갖고 있다면 정확한 실행계획을 제공하지 못하고 쿼리 수행도 최적이지 않다. 올바른 통계 정보를 갖고 있다고 해도 항상 올바른 실행계획을 제공하는 것은 아니다. 따라서 업무 지식을 바탕으로 스스로 비용을 예측하고 실행계획을 예상할 수 있어야 한다. 이를 위해 테이블 접근 순서를 정하고 인덱스 생성도를, 그리고 공정쿼리를 작성하는 것에 대해서 알아야 한다.

 

예시 쿼리

테이블 접근 순서 규칙

1. 진입형 테이블을 결정 : 쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다.

    - 사원 테이블의 성명 컬럼을 최초 진입형 조건으로 결정

2, OUTER JOIN보다 INNER JOIN을 우선 : 쿼리의 조인절에서 OUTER JOIN보다 INNER JOIN 테이블에 먼저 접근한다.

    - 조인절에서 강사 테이블보다는 과목 테이블이나 교육평가 테이블의 접근을 우선

3. 연결 확장형보다는 연결 축소형 테이블을 우선 : 쿼리의 조인절에서 연결 확장형보다 연결 축소형 테이블에 먼저 접근한다.

    - 쿼리의 조인절 테이블인 과목과 교육평가 테이블 중에서 조건(A+) 필터로 인해 레코드 축소가 일어날 가능성이 더 높은 교육평가 테이블의 접근을 우선

 

테이블 접근 순서 규칙에 의한 인덱스 생성도

 

인덱스 생성도를 통해 재작성한 공정쿼리

 

- 만능 쿼리와 한방 쿼리 DB는 집학적 사고로 접근

 

보통 일반적으로 알고 있도 가장 많이 사용되는 쿼리는 정적 쿼리이다. 정적 쿼리는 조건들이 고정돼 있으며 실행계획도 고정돼 있으므로 성능 문제 방생 시 조치와 대응이 쉽다. 반면 애플리케이션 소스상에서 사용할 조건들을 가변적으로 구성하는 동적 쿼리는 조건 자체가 가변적이기 때문에 적절한 튜닝으로 대응하기 어렵다.

 

 

위 예시에서 동적 쿼리는 7가지 조건절이 가변적으로 변하므로 128가지의 쿼리로 나눌 수 있다. 오라클 옵티마이저가 128가지의 모든 쿼리에 대해 실행계획을 제공하지만, 모든 실행계획의 성능을 보장하지는 못한다. 즉 실행계획을 완벽하게 통제하기 어렵다. 이러한 동적 쿼리는 실행계획을 통제하기 위해 UNION ALL을 이용해 쿼리를 분리하는 방법을 사용하기도 한다.

 

만능 쿼리의 경우 조건절이 가변적이지 않기 때문에 오라클 옵티마이저에게 하나의 쿼리로 인식되고, 하나의 실행계획만 제공한다. 하지만 조건값에 따라서 실제로는 128가지의 쿼리로 구분될 수 있고, 이 128가지의 쿼리가 오직 하나의 실행계획만으로 실행되는 것은 심각한 문제이다. 따라서 꼭 필요한 경우 외에는 만능 쿼리를 사용하지 않고, 핵심 조건절의 수만큼 쿼리 자체를 분리해서 사용해야 한다.

 

마지막으로 프로그램은 절차적인 구조로 작성하고, 데이터베이스 개발은 집합적인 사고로 접근하는 유연성이 필요하다. 이러한 집합적인 사고를 가지고 작성한 쿼리를 한방 쿼리라고 한다. 한방 쿼리는 개발자에게 매우 유용한 쿼리 제작 방법이다.

 

한방 쿼리 예시

 

- 오라클 옵티마이저 CBO VS RBO

 

RBO 방식 VS CBO 방식

 

RBO(Rule Based Optimizer)방식 : 규칙 기반 옵티마이저로서 미리 정해진 우선 순위 규칙에 따라 접근 경로를 결정한다. 순위가 높은 규칙이 낮은 규칙보다 우선 적용된다. 잘못된 우선 순위의 규칙이 적용되더라도 예측이 가능하며, 안정적이고 실행계획의 제어가 쉽다. 하지만 오라클 11g부터는 지원이 중단됐다.

 

CBO(Cost Based Optimizer) 방식은 비용 기반 옵티마이저로서 통계정보에 따른 비용을 계산해 가장 최소한의 비용이 소모되는 접근 경로를 결정한다. 여기에는 I/O 비용뿐만 아니라 CPU 연산 비용 및 메모리 비용까지 포함된다. CBO는 비용 방식이므로 통계정보가 잘못됐거나 최신 정보를 제대로 반영하지 못한다면, 잘못된 접근 경로를 선택할 것이다. 또한 예측 불가능하고 실행계획의 제어가 어렵다. 통계정보의 부실이나 성능 문제로 간혹 잘못된 실행계획을 제공하는 경우도 존재한다.

 

여기서 옵티마이저는 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수해할 수 있는 최적의 처리 경로를 제공하는 오라클 엔진으로, 주어진 환경(통계정보, SQL) 하에서 최적의 실행계획을 제공한다. 하지만 잘못된 SQL문이나 부정확한 통계정보에 따라 잘못된 실행계획을 제공할 수 있다. 이런 경우를 해결하기 위해서

• 첫째, 최적의 인덱스 구성
• 둘째, 올바른 SQL문 작성
• 셋째, 주기적으로 최신의 통계정보 갱신
• 넷째, 힌트절을 추가해 옵티마이저 제어

 

같은 노력을 해야한다.