제 17 장 (Oracle) 오라클에서 질의 최적화

오라클 옵티마이저 종류 규칙 기반 옵티마이저 (RBO) 비용 기반 옵티마이저 (CBO) 미리 정해진 일정한 규칙(Rule)에 따라 실행 계획을 생성한다 이 규칙에는 연산자 우선순위, 접근 경로의 우선순위, SQL 문장의 Syntax 규칙에 의한 우선 순위에 따라 여러가지 종류의 규칙이 존재함 Oracle 9i까지만 RBO를 지원하고 10g부터는 더 이상 지원하지 않음 비용 기반 옵티마이저 (CBO) SQL문을 분석하고 처리하는데 소요되는 비용을 기반으로 실행계획을 결정한다. 테이블에 저장되어 있는 데이터에 대한 각종 통계 정보(데이터 양, 인덱스 종류, 컬럼별 크기와 카디날리티등)를 기반으로 실행계획을 세운다 비용에는 데이터 파일에 대한 입/출력 횟수, CPU 사용량, 메모리 사용량, 네트워크 사용량 등이 포함된다

오라클 옵티마이저 구성요소 및 처리 단계 가능한 모든 실행 계획을 생성 각종 통계 정보를 이용한 비용 계산 최소 비용의 실행 계획을 선택

쿼리 변환기 파싱된 쿼리를 더 성능이 좋고 효율적인 쿼리로 변환함 뷰 병합 (View Merging) 쿼리 내에 존재하는 뷰를 기본 테이블로 변환하여 SQL문을 수정

쿼리 변환기 밀어넣기 (Predicate Pushing) 병합이 어려운 뷰를 처리할 때 사용함 서버쿼리 변환 서브쿼리 문장을 조인을 사용한 SQL문으로 변환 OR-Expansion WHERE 절에 OR 연산자가 포함된 여러가지 조건을 UNION ALL을 사용한 문장으로 변환하면 성능이 좋아질 수 있음

에스티메이터(Estimator) 통계 정보를 이용하여, 변환된 쿼리를 수행하기 위한 비용 계산 선택도 (Selectivity) 조건을 만족하는 Row의 비율을 말함. 카디널리티 (Cardinality) 쿼리의 실행 결과로 나오는 구별된(distinct) Row 수 비용 SQL문을 실행하는데 예상되는 CPU, 메모리와 디스크 I/O 횟수를 말함 오라클 10g부터는 비용의 단위 기준이 I/O가 아닌 처리시간(즉, CPU, 메모리와 디스크 접근, 처리하는 시간의 합)으로 바뀌어 좀 더 정확한 비용을 사용

실행계획 생성기 한 SQL 문에 대해 조인 방법이나 접근 경로에 따라 가능한 한 모든 종류의 실행계획을 만들어 냄 에스티메이터에서 계산한 비용을 근거로 가장 니용이 낮은 실행 계획을 채택함

접근 경로와 조인 방법(1) 접근 경로 Full Table Scan ROWID Scan Index Unique Scan Index Range Scan Fast Full Index Scan 쿼리에 필요한 모든 컬럼이 인덱스 컬럼인 경우, 테이블이 아닌 인덱스 자체내 에서 테이블을 Full Scan 방식으로 접근함

접근 경로와 조인 방법(2) 조인 방법 중첩 루프 조인(NLJ) 정렬 병합 조인(SML) 해쉬 조인(HJ)

옵티마이저 추적(실행계획 추적)-1 SQl*Plus에서 실행계획 보는 법

옵티마이저 추적(실행계획 추적)-2 SQl Developer에서 실행계획 보는 법 해당 문장을 선택해서 ‘Execute Explain Plan(F6) 클릭

실행계획의 분석(1) Nested Loop Join을 사용한 실행계획 분석 하위 레벨이 있으면 하위 레벨부터, 같은 레벨이면 위에서부터 아래로 처리됨

실행계획의 분석(2) 두개의 Nested Loop Join을 사용한 실행계획 예

실행계획의 분석(3) Hash Join을 사용한 실행계획 분석

실행계획의 분석(3) Sort Merge Join을 사용한 실행계획 분석

힌트 사용 (1) 힌트절의 형태 /*+ hint */ /*+ hint(argument) */ /*+ hint(argument-1, argument-20 */ 힌트의 종류 옵티마이저의 목표에 따른 힌트 접근 경로에 따른 힌트 쿼리 변환에 따른 힌트 조인 순서에 따른 힌트 조인 방법에 따른 힌트

힌트 사용 (2) 옵티마이저의 목표에 따른 힌트 /*+ ALL_ROWS */ : 질의의 모든 결과를 반환 /*+ FIRST_ROWS(n) */ : 질의의 첫 n개 결과를 반환 /*+ CHOOSE */ : 최적화 방법을 RBO에서 CBO로 변경

힌트 사용 (3) 접근 경로에 따른 힌트 /*+ FULL (table) */ : table을 full scan /*+ CLUSTER (table) */ : table을 cluster scan /*+ HASH (table) */ : table을 hash scan /*+ INDEX (table index) */ : table을 index scan /*+ NO_INDEX (table index) */ : table의 index를 사용하지 않음 /*+ INDEX_ASC (table index) */ : table을 오름차순으로 index scan /*+ INDEX_COMBINE (table index) */ : table의 비트맵 접근 경로 사용 /*+ INDEX_FFS (table index) */ : table을 fast full index scan 사용 /*+ NO_INDEX_FFS (table index) */ : fast full index scan 사용하찌 않음 /*+ INDEX_SS (table index) */ : index skip scan 사용

힌트 사용 (4) 쿼리 변환에 따른 힌트 /*+ NO_QUERY_TRANSFORMATION */ : 쿼리 변환하지 않음 /*+ USE_CONCAT */ : OR 조건을 UNION ALL로 변환 /*+ NO_EXAPAND */ : OR 조건을 UNION ALL로 변환하지 않음 /*+ REWRITE */ : MView에 대해 쿼리를 다시 작성하게 함 /*+ MERGE */ : 쿼리에 사용된 뷰들을 병합 /*+ UNNEST */ : 서브쿼리를 조인 문장으로 변환

힌트 사용 (5) 조인 순서에 따른 힌트 /*+ LEADING(table1, …) */ : 파라메터에 명시된 순서대로 테이블들을 조인 /*+ ORDERED */ : FROM절에 명신된 순서대로 조인을 수행 조인 방법에 따른 힌트 /*+ USE_NL (table) */ : NLJ 사용 /*+ USE_NL_WITH_INDEX (table index) */ : 인덱스 이용한 NLJ 사용 /*+ USE_MERGE (table) */ : SMJ 사용 /*+ USE_HASH (table) */ : 해시 조인 사용

일반적인 SQL 튜닝 기법 바인드 변수를 사용 가급적 WHERE 조건에서 인덱스 컬럼을 모두 사용 상수 값이 바뀌더라도 이전 질의를 동일하게 사용  최적화 실행 단계 줄임 가급적 WHERE 조건에서 인덱스 컬럼을 모두 사용 인덱스 컬럼에 사용하는 연산자는 가급적 동등(=) 을 사용 인덱스 컬럼은 변형을 하여 사용하지 않음 SUBSTR(last_name, 1,1) = ‘오;  last_name like ‘%오’ OR 보다는 AND를 사용 그룹핑 쿼리는 가급적 HAVING 보다는 WHERE절에서 데이터를 필터링 DISTINCT는 가급적 사용하지 않음 IN, NOT IN 대신에 EXISTS와 NOT EXISTS를 사용 SET 연산자 사용시 UNION 대신 UNION ALL을 사용