이펙티브 오라클 (2009년)
10053 이벤트를 사용하여 CBO 선택 추적하기 0 0 61,337

by 구루비스터디 10053 [2018.05.26]


  1. 10053 Event
    1. 정의
    2. 특징
    3. 실행 방법
    4. 다른 session에서 실행
    5. 구성
    6. Table 정보
    7. Index 정보
    8. Column 정보
    9. single table Base Acces Plans
    10. 테스트
    11. 결과


10053 Event

정의

  • CBO의 Optimization 과정을 Trace 파일에 출력
  • 목적 : Query Transformation이 점점 복잡해지면서 low level의 troubleshooting tool의 필요성이 제기 됨
  • query 수행 시작부터 결과를 보여주기까지의 과정을 알 수 있다.


특징

  • 10g 이전 버전 : PL/SQL 블록에 포함된 쿼리문에 대해서는 10053 트레이스 기능 작동 안 함
  • 10gR1과 10gR2의 차이점 : 섹션의 순서가 다르고, 여러 가지 설명 추가 및 시스템 통계가 기록되고, 조건절 및 개요와 함께 최종 실행 계획이 포함됨
  • Oracle의 공식문서에는 사용법 없음
  • user_dump_dest 에 "*.trc" 파일 생성
  • CBO인 경우에만 나옴 , RBO는 sql문장만 보임
  • RBO 에 Parse 되었다면 QUERY Section 이 보여지 않는다.
  • Display형태가 암호화 같이 가독성 어려움
  • CBO가 어떤 factor를 고려해서 작동하는가를 보여줌
  • Oracle Version / Parameter 변경에 따른 Optimizer 선택을 확인 할수 있다.
  • 다른 Trace 와 다르게, 10053 Level 2의 경우 Level 1 보다 적은 Trace 를 생성한다.
  • 10053 Event Trace 파일이 생성되는 조건
  • Query 는 반드시 (Hard) Parse 되어야 한다.
  • CBO 에 의해서 Parse 되어야 한다.

RBO 에 의해서 Parse 되는 3가지 이유
  • optimzer_mode 혹은 optimizer_goal 이 rule 일 경우
  • hint "rule" 이 사용된 경우
  • 문장에서 사용된 테이블 중에 통계정보도 전혀 없고, 힌트도 전혀 사용되지 않은 경우( rule 관련 힌트는 rule 뿐!!)


실행 방법

  • 현재 session에서 10053 트레이스 실행 방법

 alter session set events '10053 trace name context forever';
 alter session set events '10053 trace name context forever, level 1';
 alter session set events '10053 trace name context forever, level 2';


  • a & b번 : 같은 일을 함
  • c 번 : optimizer parameter 목록이 제외되어 약간 짧은 트레이스 파일 생성
  • 종료 : alter session set events '10053 trace name context off';


다른 session에서 실행

  • 실행 : SYS.DBMS_SYSTEM.SET_EV( <sid>, <serial#>, 10053, 1, '' )
  • 종료 ; SYS.DBMS_SYSTEM.SET_EV( <sid>, <serial#>, 10053, 0, '' )
  • Trace 파일 구분 : alter session set tracefile_identifier='10053_trace';


구성


6개의 section으로 구성
  • query
  • parameters used by the optimizer
  • Based Statistical Information
  • Base Table Access Cost
  • General Plans
  • Recosting for special features


Table 정보

10053 Event Labeldba_tables설 명
CDNNUM_ROWScardinality = 테이블의 record 수
NBLKSBLOCKSdata를 가지고 있는 block의 수 (HWM 아래의 block수)
TABLE_SCAN_CSTThe estimate cost in I/O to full-table-scan the table
AVG_ROW_LENAVG_ROW_LENo/h를 포함한 평균 record의 길이(bytes)


Index 정보

10053 Event Labeldba_index설 명
Index#, col#index의 object#, column의 column_id
LVLSBLEVELB* tree의 root block에서 leaf block까지의 depth
#LBLEAF_BLOCKSleaf block의 수
#DKDISTINCT_KEYSdistinct key의 값
LB/KAVG_LEAF_BLOCKS_PER_KEY한 index에 대한 평균 leaf block 수
DB/KAVG_DATA_BLOCKS_PER_KEY한 index에 대한 평균 data block 수
CLUFCLUSTERING_FACTORindex에 대한 data record의 정렬 정도


Column 정보

10053 Event Labeldba_tables/dba_index설 명
NDVNUM_DISTINCT전체 column의 distinct 값의 수
NULLSNUM_NULLS'null' 값을 가지고 있는 column 의 값
DENSDENSITYhistorgram이 없을경우 1/NDV
LOLOW_VALUE(numeric인경우) 최소값
HIHIGH_VALUE(numeric인경우) 최대값


single table Base Acces Plans

0parallel hint8hash cluster16partitions hint24swap inputs to join
1no access path spec9rowid lookup17nopartitions hint25fact table
2table scan10range scan backwards18anti-join26not a fact table
3index unique11rowid range scan19index rowid range scan27merge of this view
4index range12driving_site hint20bitmap index28don't push join predicate into this view
5index and-equal1321parallel_index hint29push join predicate into this view
6orderby using an index14cache hint22noparallel_index hint30no_merge of this view
7open cluster15nocache hint23index fast full scan31semi-join


테스트


결과

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3483

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입