#. Optimizer 의 종류

1. Physical Optimizer - Logical Optimizer 의 튜닝 SQL을 물리적으로 최적화
   ( 인덱스의 선택, 조인 방법의 선택, 조인 순서의 선택 )
2. Logical Optimizer - 성능향상을 위해서 옵티마이져가 SQL 을 재작성(튜닝)

#. 대상 독자

1. SQL 튜닝 책을 한 권 이상 보았거나 자신이 작성한 SQL 을 튜닝한 적이 있다.
2. 기본적인 실행 계획을 이해한다.
3. 옵티마이져에 대하여 배우고 싶다.

Part 1 . Query Transformation Concept

1.1 Logical Optimizer(Transformer)란 무엇인가 ?

• 오라클 Logical Optimizer(Transformer)란 옵티마이져의 가장 중요한 구성요소이며 성능향상을 위해 SQL을 재작성(Query Transformation)하는 역할을 담당
• 옵티마이져가 성능향상을 목적으로 SQL 재작성 하는 것

[그림1.1.1]Optimizer 구조도

• Query Transformer - SQL변신
• Cost Estimator - 통계정보 등을 참조하여 가장 낮은 Cost(비용)을 갖는 SQL 찾기
• Plan Generator - SQL 실행 계획 생성

h3.1.2 Query Transformation 을 알아야 하는 이유

• DBA 튜닝 실패 원인 ( 순위, 내용(비율) , (최근 2년간의 비율) 순서 )
  1위: Query Trannsformation 에 대한 지식 부족(49%) => ( 4%증가, 53%)
  원인 - 매뉴얼 부족!, 부족!, 부족!
  2위: Business 관점의 접근 부족, 정확한 업무파악 부족(25%) => ( 1%증가, 26%)
  3위: Wait Event 에 대한 지식 부족(15%) => ( 5%감소, 10%)
  4위: 신기술에 대한 지식 부족 혹은 부려움(8%) => 변화없음
  5위: 잘못된 액세스 패턴, 잘못된 조인 방법, 잘못된 조인 순선(3%) => 변화없음

• 오라클 성능 서적 트랜드
  #. 1990년대 : SQL의 효율적 작성법, Access Path, 조인, Object 최적화
  #. 2000년대 : OWI(Oracle Wait Interface), RAC
  #. 2010년 이후 : 옵티마이져의 내부에 대한 연구(Logical Optimizer, Physical Optimier )
  !!! 이제는 Logical Optimizer로 튜닝의 패러다임 전화 필요 !!!

1.3 Query Transformation 의 개념

• Query Transformation 은 Transformer 의 기능이며 '개발자가 작성한 SQL을 옵티마이져가 다른 모습으로 재작성하는 것'

1.3.1. SQL 재작성하는 옵티마이져의 목적

• Only 성능 향상

1.3.2. SQL 재작성하는 옵티마이져의 원칙

• 변화된 SQL은 논리적으로 원본의 SQL과 의미하는 바가 같아야 한다.

1.3.3. SQL 재작성하는 옵티마어져의 원리

1. Rule 기반의 원리

• Rule 기반의 SQL 재작성 원리, Heuristic(스스로 발견하는) Query Transformation(34개 이상)
• 불필요한 Distinct(Sort +중복제거) Operation 제거

2. Cost(비용) 기반의 원리

• 모든 SQL의 재작성 원리를 Rule로 Cover 할 수 없다.(한계)
• Cost Based Query Transformer 는 Cost Estimator 를 이용 한다. ( 비용 기반 )

1.4 Query Transformer 의 구조

그림1.4.1 옵티마이져 상세 구조도

• 요약
  **기본 ) 1~9 의 순서, 위에서 아래로 진행되며, Heuristic 에서 Cost Based 로 진행
  **예외 ) Heuristic/Cost Base Transformation 간의 종속관계(SJC 발생시, 부가적으로 JE 발생 )
  **배타 ) 점선 표시, SSU(Simple Subquery Unnesting ) 와 CSU(Complex Subquery Unnesting)의 둘 줄 하나만 실행됨
  **쿼리블록) 기본적으로 모든 Heuristic Query Transformation 은 블록(Query Block)레벨로 발생

( 쿼리 블록 예제 쿼리 )

• 쿼리블록은 무엇인가 ?
  • 옵티마이져가 최적화를 수행하는 단위로 최적의 액세스 경로와 조인순서, 조인방식을 선택하는것
  • 2개의 쿼리블록 = 서브쿼리와 메인쿼리

• 10053 Trace을 통한 쿼리블록 확인
  
  

• Optimizer 가 변경한 쿼리
  

• 인라인뷰 쿼리블록 확인
  
  

Cost Based 변환이 발생할 때마다 Cost를 구함

1. Cost Based Query Transformation(이하 CBQT)의 각 Transformation Unit 이 수행 될 때마다 Cost Estimator 가 호출됨,
2. 서로 다른 종류의 CBQT가 수행될 때 마다 Query Block 을 Cost Estimator 에게 넘겨주면 Cost Estimator 는 각각 Cost 를 계산 하여 Return
3. Plan Generator 는 Plan 생성 후, 각종 통계정보 등을 참조하여 Cost 계산

결론

가.SQL 수행 순서
1. SQL 실행
2. PARSE(SQL을 QUERY BLOCK단위로 분리)
3. Query Transformer
4. Cost Estimator
5. Plan Generator
6. Row Source Generator
7. SQL의 결과 Fetch

나. Query Transformation 은 Heuristic(Rule)과 Cost Based로 나뉨
다. 각각의 세부 Unit 들은 모두 수행될 수 있으나, 특별한 경우(배타) 둘 중 하나만 수행
라. 각각의 세부 Unit 들은 수행 순서가 정해짐
마. 기본적으로 위->아래, 왼쪽 -> 오른쪽으로 수행 ( 예외 존재 )
사. SQL 은 여러 개의 쿼리블록으로 구분(뷰/인라인뷰, 스칼라 서브쿼리),
아. HQT는 쿼리 블록마다 수행, CBQT는 발생할 때마다 Cost Estimator 를 Call 하여 비용
계산, 따라서 Hard Parsing 시 약간의 부하 발생 가능

h4.3 1.5 DBMS_XPLAN.DISPLAY_CURSOR
: 실행계획을 볼 수 있는 Tool
1. 일반적으로 10046 Event Trace + Tkprof
2. DBMS_XPLAN.DISPLAY_CURSOR 혹은 DBMS_XPLAN.DISPLAY ( Query Transformation 장점( 10046 에 비해 )
#. QUERY BLOCK NAME / QUERY ALIAS : 쿼리블럭정보
#. Outline Data : 오라클 내부(Internal) Hint
#. Predicate Information : Access 조건 및 조인 조건, Filter 조건
#. Column Projection Information : Operation id 별로 Select 된 컬럼 정보
#. Format : 자신에게 맞는 Format 설정이 자유로움

DBMS_XPLAN 사용 예)

1. GATHER_PLAN_STATISTICS 힌트을 통해서 SQL 수행

2.실제 실행 계획 확인

3. 실제 수행 계획 결과

h4.실행계획 설명
가. BASIC 항목

• Id : 각 Operation 의 ID, *가 달려 있는 Predicated Information 에 access 및 Filter 정보
• Operation :각각 실행되는 JOB
• Name :Operation 이 액세스하는 테이블 및 인덱스

나. Query Optimizer Estimations 항목(옵티마이져 예상치)

• E-Rows : 각 Operation 이 끝났을 때 return 되는 건수(예상치)
• E-Bytes : 각 Operation 이 return 한 bytes 수 (예상치)
• E-Temp : 각 Operation 이 Temporary Space 를 사용한 양 ( 예상치, 샘플엔 없음 )
• Cost(%CPU) : 각 Operation 의 Cost (예상치)
• E-Time : 수행시간(예상치)

다. Runtime Statistics 항목(실제 수행시간 및 실제 수행건수)

• Starts : 각 Operation 을 반복 수행한 건수(NL 이라면 조인 시도 횟수)
• A-Rows :각 Operation 이 Return 한 건수
• A-Time : 실제 실행시간 0.01초 까지, Child Operation 의 A-Time 을 합친 누적

라. I/O Statistics (I/O관련 READ / WRITE 한 Block 수)

• Buffers : 각 Operation 이 Memory 에서 읽은 Block 수
• Reads : 각 Operation 이 Disk 에서 Read 한 Block 수. (샘플엔 없음)
• Writes : 각 Operation 이 Disk 에 Write Block 수 ( 샘플엔 없음)

마. Memory Utilization Statistics ( Hash 작업이나 sort 작업 시 사용한 메모리 통계)

• oMem : Optimal Execution 에 필요한 Memory ( 예상치 임)
• 1Mem : One-pass Execution 에 필요한 Memory ( 예상치 임)
• O/1/M : 각 Operation 이 실행한 Optimal/One-Pass/Multipass 횟수가 순서대로 표시됨
• Used-Mem : 마직 실행 시 사용한 PGA Memory
• Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary space를 사용할 때 나타남,1024 곱해야 함.
• Max-Tmp : 메모리가 부족하여 Temporary Space 를 사용할 때 최대 Temp 사용량임, 마지막이 아닌 최대값만 보임, 1024 곱해야 함

바. 쿼리블록 정보 : Plan 상의 ID별로 쿼리블럭 및 Alias 정보 출력

사. Outline Data : 오라클이 내부적으로 사용한 힌트

아. Perdicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 나타냄

자. Column Projection Information : Plan 상의 Id 별로 Select 되는 컬럼의 정보

권고

1. 'advanced allstas last' 포맷은 출력되는 정보가 너무 많다.
2. 쿼리변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화 하다.
: 예측 Row 수(E-Row)가 생략되고 실행통계와 Predicate information 만 출력


3. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블록과 힌트정보를 추가로 출력하라.
: Query Block Name / Object Alias 정보와 Outline Data 정보가 출력됨

4. 자신만의 적절한 포맷이 필요함

1.6 내가 사용한 Hint 가 무시되는 이유

결론 :Logical Optimizer의 의한 Transformer ? Query Block 변경 ? 힌트가 무시됨 !!!

(준비)

(쿼리수행)

(실제실행계획)

조인순서를 바꿀수 있겠는가 ? V_DEPT => EMPLOYEES 에서 EMPLOYEE => V_DEPT

(힌트적용)

(실행계획확인)

• 힌트가 무시됨 원인은 Query Transformation
  • 뷰 V_dept 에 View Merging 이 발생
  • View Merging 은 Query Transformation 의 한 종류이며, 뷰를 해체하여 정상적인 조인으로 변경하는 작업, View Merging 과 같은 Query Transformation 이 발생하면 많은 경우에 쿼리 블록명이 변경됨

(해결방법)
Query Transformation 에 의해 변경된 쿼리블럭명을 지정하여 힌트를 사용하거나
Query Transformation 이 발생하지 않게 하면 힌트가 정상작동

Query Transformation 이 발생하지 않으면 조인순서 변경이 가능
(힌트가 작동, 쿼리 블록이 변경되지 않으니 )

한 단계 업그레이드 해서, 뷰 내부의 테이블에 대한 조인순서의 변경은 가능한가?

즉 조인순서를 Employee -> 뷰(Country -> Location -> Department ) 로 변경하고 싶다.
==> Global Hit 를 사용하라.

전체 SQL 에 대한 리딩, V_DEPT 에 대한 리딩, 2번의 리딩 힌트사용 ( Global Hint Dot표기법 )

Query Transformation 발생 시 힌트는 어떻게 해야 할까 ?

힌트가 작동하지 않음. 그 이유는 View Merging 이 발생하여 새로운 쿼리블록 생성

힌트에 쿼리블럭명과 Object의 Alias 를 사용해야 힌트 작동 (Global Hint,쿼리블록 표기법)

( 결과 )

뷰 내부의 테이블에 대해서만 조인순서를 바꾸고 싶다면
Query Transformation 이 발생한다고 해도 Dot 표기법을 사용할 수 있다.

1.7 10053 Event Trace

• 과거 : Access Path 와 조인순서, 조인방법이 잘못되는 경우 그 이유를 알기 위해 사용
• 최근 : Query Transformation 의 동작 추적을 위해 사용

10053 Event 발생시키는 법(user_dump_dest 디렉토리에 trace 생성됨 )

10053 이벤트 내용 순서

1) DBMS 정보, OS 정보, 하드웨어 정보 그리고 SQL 을 실행한 Client 정보
2) 쿼리블럭 정보와 수행된 SQL을 Parse 로 부터 받아서 출력한다.
3) 10053 Event 에서 사용될 용어 출력
4) Optimizer 에 관련된 성능 파라미터를 출력, Bug Fix Control 정보 출력
Default 값을 기본적으로 출력하되, 수정된 파라미터가 있을 경우 따로 출력
SQL 에서 opt_param 힌트를 사용 시, 따로 출력
5) Heuristic Query Transformation(이하 HQT)과정 출력
6) Bind Peeking 이 수행
7) Cost Based Query Transformation(이하 CBQT)과정 출력
연이어 HQT 혹은 CBQT 로 인해 신규로 생성된 쿼리블럭 정보 출력
8) 시스템 통계정보와 테이블과 인덱스의 통계정보 출력
9) 테이블 단위로 최적의 Access Path 와 Cost 출력
10) 최적의 조인방법과 조인순서 정함
11) SQL Dump 와 Explain Plan Dump 를 수행 하여 SQL과 실행계획을 출력
12) 마지막으로 Optimizer state dump 와 Hint dump 를 수행하여 SQL 이 수행되었던
당시의 옵티마이져 관련 파라미터 정보, Bug Fix Control, 최종 적용된 힌트 정보 출력

• Query Transformation : 5번과 7번
• Physical Transformation : 9번과 10번

Simple Case Study

1) DBMS 정보, OS 정보, 하드웨어 정보 그리고 SQL 을 실행한 Client 정보

2) 쿼리블럭 정보와 수행된 SQL을 Parse 로 부터 받아서 출력한다.

3) 10053 Event 에서 사용될 용어 출력

4) Optimizer에 관련된 성능 파라미터를 출력하고 연이어 bug Fix Control 정보를 출력한다.

5) Heuristic Query Transformation 과정이 출력된다.

6) Bind Peeking이 수행된다.

7) Cost Based Query Transformation 과정이 출력된다.

8) 시스템 통게정보와 테이블과 인덱스의 통계정보를 출력한다.

9) 테이블 단위로 최적의 Access path와 Cost를 출력한다.

10) 최적의 조인방법과 조인순서를 정한다.

11) SQL dump와 Explain Plan Dump를 수행하여 SQL이 수행되었던 당시의 SQL과 실행계획을 출력한다.

12) 마지막으로 Optimizer state dump를 수행하여 SQL이 수행되었던 당시의 옵티마이져 관련 파라미터 정보, Bug Fix Control 등을 출력 한다.