비용기반의 오라클 원리 (2009년)
10053 트레이스 파일 0 0 53,237

by 구루비스터디 10053 10053 TRACE [2018.09.27]


  1. 10053 트레이스 파일
    1. 가. 10053 이란
    2. 나. 10053 Trace 화일 얻기
      1. 나.1 10053 트레이스 파일 얻는 방법(Plan_Table 사용)(1)
      2. 나.2 10053 트레이스 파일 얻는 방법(Using Bind 변수)(2)
      3. 나.3 10053 트레이스 파일 얻는 방법(My Session VS Other Session)(3)
    3. 다. 10053 Trace 화일 찾기
    4. 라. 10053 내용
      1. 라.1 10053 내용 [ QUERY ]
      2. 라.2 10053 내용 [ PARAMETERS USED BY THE OPTIMIZER ]
      3. 라.3 10053 내용 [ BASE STATISTICAL INFORMATION ]
  2. 라.4 10053 내용 [ BASE TABLE ACCESS COST ]
    1. 라.5 10053 내용 [ GENERAL PLANS ]


10053 트레이스 파일

가. 10053 이란


- 10053 Event 를 통해서 Optimizer가 내부적인 비용 계산에 대한 Trace 를 얻는게 가능하다.
- CBO 에 의해서 만들어지는 내부적(Optimizer) 결정에 대한 검증이 필요한 Support,
  이 경우 10053 Event Trace 를 이용하여, Optimizer 결정에 대한 Trace 를 얻을수 있다.
  10053 Event Trace 는 RBO 에 의한 최적화 작업 쿼리에 대해서는 효과가 없습니다.
- Oracle Version / Parameter 변경에 따른 Optimizer 선택을 확인 할수 있다.
- 다른 Trace 와 다르게, 10053 Level 2의 경우 Level 1 보다 적은 Trace 를 생성한다.
- 10053 Event Trace 화일이 생성되는 조건
  1. Query 는 반드시 (Hard) Parse 되어야 한다.
  2. CBO 에 의해서 Parse 되어야 한다.


나. 10053 Trace 화일 얻기

나.1 10053 트레이스 파일 얻는 방법(Plan_Table 사용)(1)


 - PLAN_TABLE 이 존재하는지 확인
  ( 없다면 $ORACLE_HOME/rdbms/admin/utlxplan.sql 을 통해서 생성 ) 
 SQL> alter session set max_dump_file_size = unlimited ;
 SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
 Session Altered.
 
 SQL> EXPLAIN PLAN FOR -- SQL STATEMENT 
 Explained.
 SQL> exit
  
- PLAN_TABLE 이 존재하고, 
  EXPLAIN PLAN 을 수행하면 해당 SQL 문장이 강제로 REPARSE 된다.


나.2 10053 트레이스 파일 얻는 방법(Using Bind 변수)(2)


 SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
 Session altered.
 -- set up binds.
 SQL> variable a number
 SQL> variable b varchar2(10)
 -- assign values to binds
 SQL> Begin
 :a :=20;
 :b :='CLERK';
 end ;
 /
 
  SQL> select empno, ename, mgr
  from emp
  where detpn = :a
  end job job = :b
  /

  방법 2의 경우 SQL 문장이 반드시 Parse 되어야 10053 Trace 내용이 생성된다.
  따라서 아래와 같이 SQL 문을 변형한다. ( Space 추가, 혹은 주석 추가 )
  SQL > select /* 10053 trace #1 */ 

SQLPLUS 에서의 Bind 변수 Type 에 대한 제약
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                      VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                      NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                      BINARY_FLOAT | BINARY_DOUBLE ] ]    


나.3 10053 트레이스 파일 얻는 방법(My Session VS Other Session)(3)


- My Session
   A. ON
      ALTER SESSION SET EVENTS '10053 trace name context forever[, level {1|2}]'
   B. OFF
      ALTER SESSION SET EVENTS '10053 trace name context off'
- Other Session
   A. ON
      SYS.DBMS_SYSTEM.SET_EV(<sid>,<serial#>,10053,{1|2},")
   B. OFF
      SYS.DBMS_SYSTEM.SET_EV(<sid>,<serial#>,10053,0,")


다. 10053 Trace 화일 찾기


  - USER_DUMP_DEST 에 존재
  - Trace 화일 구분을 쉽게 하기 위해서 구분자를 지정 하자
  SQL> alter session set tracefile_identifier='10053_TG';


라. 10053 내용


10053 TRACE 는 아래와 같이  6부분으로 구성된다.
1. QUERY
2. PARAMETERS USED BY THE OPTIMIZER
3. BASE STATISTICAL INFORMATION
4. BASE TABLE ACCESS COST
5. GENERAL PLANS
6. RECOSTING FOR SPECIAL FEATURES

10053 Trace 를 위한 예제 문장

SQL> exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS' , DEGREE=>5,CASCADE=>TRUE);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'DEPT',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS' , DEGREE=>5,CASCADE=>TRUE);
PL/SQL procedure successfully completed.

SQL>  alter session set events '10053 trace name context forever, level 1';
Session altered.

SQL> variable b1 varchar2(30)
SQL> begin
  2  :b1 := 'KING';
  3  end ;
  4  /

PL/SQL procedure successfully completed.

SQL> select /*+ 10053 */ dname, ename
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and ename = :b1 ;

DNAME          ENAME
-------------- ----------
ACCOUNTING     KING

SQL> exit



라.1 10053 내용 QUERY


Parse 된 SQL 문장을 보여준다.
만일 RBO 에 Parse 되었다면 QUERY Section 이 보여지 않는다.
RBO 에 의해서 Parse 되는 3가지 이유
 1. optimzer_mode 혹은 optimizer_goal 이 rule 일 경우
 2. hint "rule" 이 사용된 경우
 3. 문장에서 사용된 테이블 중에 단 하나도 통계정보다 없고, 
    어떤 힌트도 사용되지 않은 경우 ( rule 관련 힌트는 rule 뿐이다. )



*** 2009-09-22 15:11:09.758
*** ACTION NAME:() 2009-09-22 15:11:09.757
*** MODULE NAME:(SQL*Plus) 2009-09-22 15:11:09.757
*** SERVICE NAME:(SYS$USERS) 2009-09-22 15:11:09.757
*** SESSION ID:(1023.61386) 2009-09-22 15:11:09.757
Registered qb: SEL$1 0xbf395458 (PARSER)
  signature (): qb_name=SEL$1 nbfros=2 flg=0
    fro(0): flg=4 objn=176835 hint_alias="DEPT"@"SEL$1"
    fro(1): flg=4 objn=176834 hint_alias="EMP"@"SEL$1"

SQL> select owner, object_name, object_type, object_id
     from dba_objects where object_id in (176835,176834) ;

OWNER                OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID
-------------------- ------------------------------ ------------------- ----------
SCOTT                EMP                            TABLE                   176834
SCOTT                DEPT                           TABLE                   176835
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks failed for 3jd0tkrf6hkp7.
Query block (9fffffffbf395458) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "DEPT"."DNAME" "DNAME","EMP"."ENAME" "ENAME" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
Query block (9fffffffbf395458) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
Query block (9fffffffbf395458) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "DEPT"."DNAME" "DNAME","EMP"."ENAME" "ENAME" FROM "SCOTT"."EMP" "EMP","SCOTT"."DEPT" "DEPT" WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
Query block (9fffffffbf395458) unchanged
CBQT: Validity checks failed for 3jd0tkrf6hkp7.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
PM:     PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :
         "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
after transitive predicate generation: "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
finally: "EMP"."DEPTNO"="DEPT"."DEPTNO" AND "EMP"."ENAME"=:B1
apadrv-start: call(in-use=1088, alloc=16344), compile(in-use=40112, alloc=44008)
kkoqbc-start
            : call(in-use=1096, alloc=16344), compile(in-use=41304, alloc=44008)
kkoqbc-subheap (create addr=9fffffffbf39c018)
******************************************
Current SQL statement for this session:
select dname, ename
from emp, dept
where emp.deptno = dept.deptno
and ename = :b1 
*******************************************
....
중략
....
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=871 siz=32 off=0
  No bind buffers allocated



라.2 10053 내용 PARAMETERS USED BY THE OPTIMIZER


: Access Plan 에 영향을 줄수 있는 Optimizer Parameter List 를 보여준다.



***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  _pga_max_size                       = 332580 KB
  optimizer_mode                      = first_rows
  _complex_view_merging               = false
  _optim_peek_user_binds              = false
  _optimizer_sortmerge_join_enabled   = false
  _optimizer_push_pred_cost_based     = false
  _bloom_filter_enabled               = false
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled  
  ...
  중략
  ...
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0  
  ...
  중략
  ...
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  ...
  중략
  ...
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select dname, ename
from emp, dept
where emp.deptno = dept.deptno
and ename = :b1 
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
  fro(0): flg=0 objn=176835 hint_alias="DEPT"@"SEL$1"
  fro(1): flg=0 objn=176834 hint_alias="EMP"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 841 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************


라.3 10053 내용 BASE STATISTICAL INFORMATION


: : 쿼리에서 사용된 테이블과 인덱스에 대한 기본 정보 ( PK 구성 칼럼 정보 )
  Trace 에서 사용된 주요 단어
  For Tables  
  CND   => dba_tables.NUM_ROWS          : The Cardinality = number of rows of the table
  NBLKS => dba_tables.BLOCKS            : The number of blocks below the hig water mark
  TABLE_SCAN_CST                        : The estimated cost in I/O to full-table-scan the table
  AVG_ROW_LEN => dba_tables.AVG_ROW_LEN : The average length of a row
  
  For Indexes 
  Index #, Col#                         : The Object# of the index and the column_id of the columns
                                 Oracle 9 brings an improvement by using the index name rather than index#
  LVLS => dba_indexes.BLEVEL            : The Height of the index b-tree
  #LB  => dba_indexes.LEAF_BLOCKS       : The number of leaf blocks
  #DK  => dba_indexes.DISTINCT_KEYS     : The number of disinct keys of the index
  LB/K => dba_indexes.AVG_LEAF_BLOCKS_PER_KEY : The average number of leaf blocks per key
  DB/K => dba_indexes.AVG_DATA_BLOCKS_PER_KEY : The average number of data blocks per key 
  CLUF => dba_indexes.CLUSTERINF_FACTOR : The clustering factor of the index    



- 통계정보가 없는 경우 
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DEPT  Alias: DEPT  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
  Column (#1): DEPTNO(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 13.00 NDV: 3 Nulls: 0 Density: 0.39024 
***********************
Table Stats::
  Table: EMP  Alias: EMP  (NOT ANALYZED)
    #Rows: 82  #Blks:  1  AvgRowLen:  100.00
  Column (#8): DEPTNO(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 13.00 NDV: 3 Nulls: 0 Density: 0.39024
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------



- 통계정보가 있는 경우
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: DEPT  Alias: DEPT
    #Rows: 4  #Blks:  60  AvgRowLen:  20.00
  Column (#1): DEPTNO(NUMBER)
    AvgLen: 3.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 10 Max: 40
***********************
Table Stats::
  Table: EMP  Alias: EMP
    #Rows: 14  #Blks:  60  AvgRowLen:  37.00
  Column (#8): DEPTNO(NUMBER)
    AvgLen: 3.00 NDV: 3 Nulls: 0 Density: 0.10714 Min: 10 Max: 30
    Histogram: HtBal  #Bkts: 14  UncompBkts: 14  EndPtVals: 3  


라.4 10053 내용 BASE TABLE ACCESS COST


: SQL 문장내에서 사용된 테이블의 Access 비용을 살펴본다.
  Join 절을 제외한 모든 사용가능한 Predicate 절을 고려 대상으로 삼는다.
  즉, 테이블별 독립적으로 접근가능한 Access Cost 를 계산한다.



BASE ACCESS PLANS

0  PARALLEL HINT
1  NO ACCESS PATH SPEC
2  TABLE SCAN
3  INDEX UNIQUE
4  INDEX RANGE
5  INDEX AND EQUAL
6  ORDER BY USING AN INDEX
7  OPEN CLUSTER
8  HASH CLUSTER
9  ROWID LOOKUP
10 RANGE SCAN BACKWARDS
11 ROWID RANGE SCAN
12 DRIVING_SITE HINT
13 
14 CACHE HINT
15 NOCACHE HINT
16 PARTITIONS HINT
17 NOPARTITIONS HINT
18 ANTI-JOIN
19 INDEX ROWID RANGE SCAN
20 BITMAP INDEX
21 PARALLEL_INDEX HINT
22 NOPARALLEL_INDEX HINT
23 INDEX FAST FULL SCAN
24 SWAP INPUTS TO JOIN
25 FACT TABLE
26 NOT A FACT TABLE
27 MERGE OF THIS VIEW
28 DON'T PUSH JOIN PREDICATE INTO THIS VIEW
29 PUSH JOIN PREDICATE INTO THIE VIEW
30 NO_MERGE OF THIS VIEW
31 SEMI-JOIN



- WHERE 절에 존재하는 칼럼에 대한 통계정보가 보여진다.
- QUERY 절에서 반복적으로 테이블을 사용( 다른 PREDICATE 정보를 사용 )하면
- 각각의 SINGLE TABLE ACCESS PATH 정보가 보여진다.
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): ENAME(VARCHAR2)
    AvgLen: 6.00 NDV: 14 Nulls: 0 Density: 0.071429
  Table: EMP  Alias: EMP     
    Card: Original: 14  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  15.04  Resp: 15.04  Degree: 0
      Cost_io: 15.00  Cost_cpu: 430486
      Resp_io: 15.00  Resp_cpu: 430486
  Best:: AccessPath: TableScan
         Cost: 15.04  Degree: 1  Resp: 15.04  Card: 1.00  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: DEPT  Alias: DEPT     
    Card: Original: 4  Rounded: 4  Computed: 4.00  Non Adjusted: 4.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  15.04  Resp: 15.04  Degree: 0
      Cost_io: 15.00  Cost_cpu: 427966
      Resp_io: 15.00  Resp_cpu: 427966
  Best:: AccessPath: TableScan
         Cost: 15.04  Degree: 1  Resp: 15.04  Card: 4.00  Bytes: 0


라.5 10053 내용 GENERAL PLANS


: Optimizer 는 이섹션을 통해서, Join 방법, Join 순서에 따른 비용을 살펴본다.
  그리고 가장 최선의 계획을 생성한다.



NL - Nested Loop Join
     Join Cost = cost of accessing outer table 
               + ( cardinality of outer table * cost of accessing inner table )
SM - Sort Merge Join
     Join Cost = ( cost of accessing outer table + outer sort cost ) 
               + ( cost of accessing inner table + inner sort cost ) 
HA - HASH JOIN
     Join Cost = ( cost of accessing outer table )
               + ( cost of building hash table )
               + ( cost of accessing inner table )



***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  EMP[EMP]#0  DEPT[DEPT]#1
***************
Now joining: DEPT[DEPT]#1
***************
NL Join
  Outer table: Card: 1.00  Cost: 15.04  Resp: 15.04  Degree: 1  Bytes: 9
  Inner table: DEPT  Alias: DEPT
  Access Path: TableScan
    NL Join:  Cost: 30.09  Resp: 30.09  Degree: 1
      Cost_io: 30.00  Cost_cpu: 858453
      Resp_io: 30.00  Resp_cpu: 858453
  Best NL cost: 30.09
          resc: 30.09 resc_io: 30.00 resc_cpu: 858453
          resp: 30.09 resp_io: 30.00 resp_cpu: 858453
Join Card:  1.00 = outer (1.00) * inner (4.00) * sel (0.25)
Join Card - Rounded: 1 Computed: 1.00
HA Join
  Outer table: 
    resc: 15.04  card 1.00  bytes: 9  deg: 1  resp: 15.04
  Inner table: DEPT  Alias: DEPT
    resc: 15.04  card: 4.00  bytes: 13  deg: 1  resp: 15.04
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.50  #ptns: 1
    hash_area: 2 (max=256)   Hash join: Resc: 30.59  Resp: 30.59  [multiMatchCost=0.00]
  HA cost: 30.59
     resc: 30.59 resc_io: 30.00 resc_cpu: 5905691
     resp: 30.59 resp_io: 30.00 resp_cpu: 5905691
Best:: JoinMethod: NestedLoop
       Cost: 30.09  Degree: 1  Resp: 30.09  Card: 1.00  Bytes: 22
***********************
Best so far: Table#: 0  cost: 15.0427  card: 1.0000  bytes: 9
             Table#: 1  cost: 30.0851  card: 1.0000  bytes: 22

"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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