To be a Oracle architect. RUN~!!

출처 : https://theone79.tistory.com/865

1. 10046 Event가 갖지 못한 DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR의 장점

2. DBMS_XPLAN

3. DBMS_XPLAN.DISPLAY_CURSOR

• Function Parameter
• 파라미터 설명
• FORMAT 옵션
  

  구분	FORMAT	내용
  예측 내용	BASIC	가장 기본적인 정보만 보여준다.
  	TYPICAL	FORMAT의 DEFAULT 값인 TYPICAL은 SQL 투닝에 필요한 NORMAL한 정보를 보여 준다. SQL 튜닝에 가장 유용하게 사용되는 PREDICATE INFORMATION이 제공된다.
  	ALL	TYPICAL  FORMAT에 QUERY BLOCK NAME과 COLUMN PROJECTION INFORMATION이 추가로 제공된다.
  	OUTLINE	TYPICAL FORMAT에 추가적으로 HIDDEN HINT인 OUTLINE GLOBAL HINT를 제공한다.
  	ADVANCE	ALL FORMAT에 OUTLINE FORMAT를 합친 정보를 제공한다.
  실측 내용	ALLSTATS	실제 엑세스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		수행횟수에 따라 누적된 값을 보여준다.
  	ALLATATS LAST	실제 엑스스한 로우수와 수행시간, CP, PR, PW 정보를 보여준다.
  		마지막에 수행된 값을 보여준다.
  	ADVANCEDALLSTATS LSAT	DBMS_XPLAN.DISPLAY_CURSOR에서 지원하는 모든 FORMAT의 정보를 보여준다.

4. DBMS_XPLAN.DISPLAY_CURSOR 사용방법

• PLAN 정보 기록
  • 'GATHER_PLAN_STATISTICS' HINT 사용
  • STSTISTICS_LEVEL를 ALL로 설정
  • '_ROWWOURCE_EXECUTION_STATISTICS' 파라미터를 TRUE로 설정
• PLAN 정보 조회
  
  • SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ADVANCED ALLSTATS LAST')) 

5. Plan 상의 항목들에 대한 설명

1) Basics 항목

• Id : 각 Operationd의 ID임. *가 달려있는 경우는 Predicate Informatio에 Access 및 Filter에 관한 정보를 표시함
• Operation : 각각 실행되는 JOB
• Name : Operationdl 엑세스 하는 Table 및 Index

2) Query Optimizer Estimations 항목(예상치)

• E-Rows : 각 Operation이 끝났을 때 return 되는 건수.
• E-Bytes : 각 Operation이 Temporany Space를 사용한 양
• Cost(%CPU) : 각 Operation의 Cost. 괄호 안의 내용은 CPU Cost의 백분율임. 이 값은 Child Operation의 Cost를 합친 누적치.
• E-Time : 수행시간

3) Runtime Statistics 항목

• Starts : 각 Operation을 반복 수행한 건수
• A-Rows : 각 Operation이 Return 한 건수
• A-Time : 실제 실행시간. 0.01초까지 나타남(HH:MM:SS.FF).  Child Operation의 A-Time을 합친 누적치

4) I/O Statistics

• Buffers : 각 Operation이 memory에서 읽은 Block 수.
• Reads : 각 Operation이 Disk에서 Read한 Block 수.
• Writes : 각 Operation이 Disk에서 White한 Block 수.

5) Memory Utilization Statistics(hash 작업이나 sort 작업 시 사용한 메모리 통계)

• OMen : Optimal Execution에 필요한 Memory
• 1Mem : One-pass Execution에 필요한 Momory
• O/1/M : 각 Operation이 실행한 Optmal/One-pass/Multipass 횟수가 순서대로 표시됨
              O 일 경우 메모리공간(hash_area_size)이 부족하지 않아 temp 영역(disk)을 
              사용하지 않고 처리 되었다는 의미임. 
• Used-mem : 마지막 실행 시 사용한 PGA -Memory
• Used-Tmp : 마지막 실행 시 메모리가 부족하여 Temporary Space를 대신 사용할 때 나타남. 보이는 값에 1024를 곱해야 함.
• Max-Tmp : 메모리가 부족하여 Temporary Space를 사용할 때 최대 Temp 사용량. Used-Tmp와 다른 점은 마지막 수행시가 아니라 SQL을 여러 번 수행했을 경우에 항상 최대값만 보인다는 것. 보이는 값에 1024를 곱해야 함.

6) 쿼리블록 정보 : Plan 상의 Id별로 쿼리블럭 및 Alias 정보를 출력.,

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

8) Predicate Information : Plan 상의 Id 별로 인덱스 액세스, Filter, 조인정보 등을 표출

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

6. 쿼리 변형이 없는 단순 쿼리 튜닝의 경우는 최대한 단순화.

• SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +PREDICATE'))  
• Format : 'allstats last -rows +predicate'로 설정

7.쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리블럭과 힌트정보를 추가로 출력

• SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_CURSOR( NULL, NULL, 'ALLSTATS LAST -ROWS +ALIAS +OUTLINE +PREDICATE'))  
• Format : 'allstats last -rows +alias +outline +predicate'로 설정
• Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력
• +alias : 쿼리블록 추가
• +outline : 오라클리 내부적으로 사용한 힌트정보를 출력