비용기반의 오라클 원리 (2009년)
- 10053 트레이스 파일
- 라.4 10053 내용 [ BASE TABLE ACCESS COST ]
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.