Oracle EVENT 10053(CBOトレース)の出力例

概要

Oracleの10053(CBO)トレースを読み解く際の確認すべきセクションを記載します。

各セクション

ヘッダー(Oracle/OS/ロケーション/CPU/メモリ)

Trace file /opt/oracle/diag/rdbms/ora12201/ora12201/trace/ora12201_ora_9374.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /opt/oracle/product/12.2.0.1/dbhome_1
System name: Linux
Node name: b4eb3c10ed7d
Release: 3.10.0-862.2.3.el7.x86_64
Version: #1 SMP Wed May 9 18:05:47 UTC 2018
Machine: x86_64
Instance name: ora12201
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 9374, image: oracle@b4eb3c10ed7d

セッション情報(セッションID/MODULE/ACTION)

*** 2018-05-28T13:43:50.210067+00:00 (PDB1(3))
*** SESSION ID:(21.61083) 2018-05-28T13:43:50.210095+00:00
*** CLIENT ID:() 2018-05-28T13:43:50.210108+00:00
*** SERVICE NAME:(pdb1) 2018-05-28T13:43:50.210112+00:00
*** MODULE NAME:(SQL*Plus) 2018-05-28T13:43:50.210115+00:00
*** ACTION NAME:() 2018-05-28T13:43:50.210119+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2018-05-28T13:43:50.210172+00:00
*** CONTAINER ID:(3) 2018-05-28T13:43:50.210179+00:00

クエリブロックとエイリアス

Registered qb: SEL$1 0xf54998a0 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
   signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=73825 hint_alias="EMPLOYEES"@"SEL$1"Registered qb: SEL$2 0xf5490e00 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
   signature (): qb_name=SEL$2 nbfros=1 flg=0
   fro(0): flg=4 objn=73825 hint_alias="EMPLOYEES"@"SEL$2"
SPM: statement not found in SMB
SPM: capture of plan baseline is OFF

qbはQUERY BLOCK(問合せブロック)の略です。今回の出力内容では2つのクエリブロック(SEL$1/SEL$2）が出力されています。
共にEMPLOYEESテーブルで同一オブジェクト(objn=73825)ですが、主問合せとサブクエリのクエリブロックが分かれている形になります。
なお、下部のSPMのメッセージはSQL計画管理(SPM)です。

ADOP(自動並列度)

**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
kkopqSetForceParallelProperties: Hint:no
Query: compute:yes forced:no forceDop:0
Global Manual Dop: 1 - Rounded?: no

上記例ではADOPが機能していないことを表しています。

述語移動

PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION

predicate(述語)とは問合せ条件の単位です。
問合せ変換(最適化)として、A表とB表のJOINをする場合にA表に指定された条件がB表にも適用可能な時にはCBOはB表にも同じ条件を追加することで、JOINの対象行を減らすという最適化を試行します。
今回は、出力の最後が「(#0)」つまり0件となっているので、試行したが対象は存在しなかったということを表しています。

解析対象のSQL文の表示

----- Current SQL Statement for this session (sql_id=43s9nuk0zjx4w) -----
select
    /*+ hard parse */
    first_name,
    last_name,
    hire_Date
from
    employees
where
    hire_date in(
        select
            hire_date
        from
            employees
        where
            department_id = 30
)

トレース内の略語リストと説明

Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
CBY - connect by
SLP - select list pruning
DP - distinct placement
VT - vector transformation
AAT - Approximate Aggregate Transformation
ORE - CBQT OR-Expansion
LORE - Legacy OR-Expansion
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
128: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
256: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans

これらの情報は、先述の「Predicate Move-Around (PM)」を確認する際に使用します。

パラメータのリスト(変更済/デフォルト/ヒント)

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
:
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
Compilation Environment Dump
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
:
(中略)
:
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************

オプティマイザの動作に関するパラメータの一覧です。※全て出力されるわけではありません。

クエリブロックの変換

Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$2 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$2 (#0)
TE: Bypassed: No partitioned table in query block.
ORE: Checking validity of OR Expansion for query block SEL$2 (#0)
ORE: Predicate chain before QB validity check - SEL$2
"EMPLOYEES"."DEPARTMENT_ID"=30
ORE: Predicate chain after QB validity check - SEL$2
"EMPLOYEES"."DEPARTMENT_ID"=30
ORE: bypassed - No valid predicate for OR expansion.
VT: Initial VT validity check for query block SEL$2 (#0)
VT: Bypassed: inmemory is disabled.
BJ: Checking validity for bushy join for query block SEL$2 (#0)
invalid because Not enabled by hint/parameter
BJ: Bypassed: Not enabled by hint/parameter.
CBQT: Validity checks passed for 43s9nuk0zjx4w.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
:

バインド・ピークに関する情報

*******************************************
Peeked values of the binds in SQL statement
*******************************************

今回はバインド変数を使用していないので特に出力はありません。

問合せ変換(最適化)後のSQL

*************************
Common Subexpression elimination (CSE)
*************************
CSE: CSE not performed on query block SEL$5DA710D3 (#1).
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "EMPLOYEES"."FIRST_NAME" "FIRST_NAME","EMPLOYEES"."LAST_NAME" "LAST_NAME","EMPLOYEES"."HIRE_DATE" "HIRE_DATE" FROM "HR"."EMPLOYEES" "EMPLOYEES","HR"."EMPLOYEES" "EMPLOYEES" WHERE "EMPLOYEES"."HIRE_DATE"="EMPLOYEES"."HIRE_DATE" AND "EMPLOYEES"."DEPARTMENT_ID"=30
kkoqbc: optimizing query block SEL$5DA710D3 (#1)

:
call(in-use=4392, alloc=16344), compile(in-use=179064, alloc=183040), execution(in-use=3200, alloc=4032)

実行SQL

select
/*+ hard parse */
first_name,
last_name,
hire_Date
from
employees
      where
hire_date in(
     select
                    hire_date
                  from
employees
where
   department_id = 30
   )
;

変換後SQL

SELECT
           "EMPLOYEES"."FIRST_NAME" "FIRST_NAME",
           "EMPLOYEES"."LAST_NAME" "LAST_NAME",
           "EMPLOYEES"."HIRE_DATE" "HIRE_DATE"
FROM
           "HR"."EMPLOYEES" "EMPLOYEES",
           "HR"."EMPLOYEES" "EMPLOYEES"
WHERE
          "EMPLOYEES"."HIRE_DATE" = "EMPLOYEES"."HIRE_DATE"
AND "EMPLOYEES"."DEPARTMENT_ID" = 30

システム統計に関する表示

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using dictionary system stats.
Using NOWORKLOAD Stats
CPUSPEEDNW: 2923 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)

CPUSPEEDNW：CPU速度(MHz)
IOSEEKTIM：シークタイム + OSオーバーヘッドの時間(ミリ秒)
IOTFRSPEED：1ブロックの平均転送速度(bytes/millisec)
MBRC：マルチブロックI/O時の読み込みブロック数の平均

オブジェクト統計

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMPLOYEES Alias: EMPLOYEES
#Rows: 107 SSZ: 0 LGR: 0 #Blks: 5 AvgRowLen: 69.00 NEB: 0 ChainCnt: 0.00 ScanRate: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
Column (#6): HIRE_DATE(DATE)
AvgLen: 8 NDV: 98 Nulls: 0 Density: 0.010204 Min: 2451923.000000 Max: 2454578.000000
Index Stats::
Index: EMP_DEPARTMENT_IX Col#: 11
LVLS: 0 #LB: 1 #DK: 11 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00 NRW: 106.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: EMP_EMAIL_UK Col#: 4
LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 19.00 NRW: 107.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: EMP_EMP_ID_PK Col#: 1
LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 2.00 NRW: 107.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: EMP_JOB_IX Col#: 7
LVLS: 0 #LB: 1 #DK: 19 LB/K: 1.00 DB/K: 1.00 CLUF: 8.00 NRW: 107.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: EMP_MANAGER_IX Col#: 10
LVLS: 0 #LB: 1 #DK: 18 LB/K: 1.00 DB/K: 1.00 CLUF: 7.00 NRW: 106.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
Index: EMP_NAME_IX Col#: 3 2
LVLS: 0 #LB: 1 #DK: 107 LB/K: 1.00 DB/K: 1.00 CLUF: 15.00 NRW: 107.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 8192 BSZ: 1
KKEISFLG: 1
:

Table Stats::

Table:テーブル名
Alias:テーブルの別名
#Rows:行数
#Blks:HWMまでのブロック数
AvgRowLen:平均行長
ChainCnt:行連鎖の数
Column (列番号): 列名(属性)
AvgLen:平均列長
NDV:ユニークな値の数
Nulls:NULL値の数
Density:列の密度
Min:列の最小値
Max:列の最大値

Index Stats::

Index:索引名
Col#:列番号
LVLS:索引の高さ
#LB:リーフ・ブロック数
#DK:キー値の種類(DISTINCT_KEYS)
LB/K:1つのキー値に占めるリーフ･ブロック数
DB/K:1つのキー値が参照している表のブロック数
CLUF:クラスタ化係数

テーブルアクセスパスの解析情報

Access path analysis for EMPLOYEES
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMPLOYEES[EMPLOYEES]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"EMPLOYEES"."DEPARTMENT_ID"=30
Column (#11):
NewDensity:0.004717, OldDensity:0.004717 BktCnt:106.000000, PopBktCnt:103.000000, PopValCnt:8, NDV:11
Column (#11): DEPARTMENT_ID(NUMBER)
AvgLen: 3 NDV: 11 Nulls: 1 Density: 0.004717 Min: 10.000000 Max: 110.000000
Histogram: Freq #Bkts: 11 UncompBkts: 106 EndPtVals: 11 ActualVal: yes
Estimated selectivity: 0.056604 , endpoint value predicate, col: #11
Table: EMPLOYEES Alias: EMPLOYEES
Card: Original: 107.000000 Rounded: 6 Computed: 6.000000 Non Adjusted: 6.000000
Scan IO Cost (Disk) = 3.000000
Scan CPU Cost (Disk) = 73057.200000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.056604 flag = 2048 ("EMPLOYEES"."DEPARTMENT_ID"=30)
Total Scan IO Cost =   3.000000 (scan (Disk))
                       + 0.000000 (io filter eval) (= 0.000000 (per row) * 107.000000 (#rows))
                     =   3.000000
Total Scan CPU Cost = 73057.200000 (scan (Disk))
                       + 5350.000000 (cpu filter eval) (= 50.000000 (per row) * 107.000000 (#rows))
                      = 78407.200000
Access Path: TableScan
Cost: 3.002235 Resp: 3.002235 Degree: 0
Cost_io: 3.000000 Cost_cpu: 78407
Resp_io: 3.000000 Resp_cpu: 78407
****** Costing Index EMP_DEPARTMENT_IX
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.056604 , endpoint value predicate, col: #11
Access Path: index (AllEqRange)
Index: EMP_DEPARTMENT_IX
resc_io: 2.000000 resc_cpu: 17543
ix_sel: 0.056604 ix_sel_with_filters: 0.056604
Cost: 2.000500 Resp: 2.000500 Degree: 1
Best:: AccessPath: IndexRange
Index: EMP_DEPARTMENT_IX
   Cost: 2.000500 Degree: 1 Resp: 2.000500 Card: 6.000000 Bytes: 0.000000
:

上記の出力内容からTableScanコスト(3.002235)とindex scanのコスト(2.000500)を比較し、コストの低いindex scanがBestAccessPathとして選択されています。

可能な結合方法の解析

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: EMPLOYEES[EMPLOYEES]#0 EMPLOYEES[EMPLOYEES]#1
***************
Now joining: EMPLOYEES[EMPLOYEES]#1
***************
NL Join
Outer table: Card: 107.000000 Cost: 3.001778 Resp: 3.001778 Degree: 1 Bytes:
Access path analysis for EMPLOYEES
Scan IO Cost (Disk) = 1.373832
Scan CPU Cost (Disk) = 73057.200000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.010204 flag = 2048 ("EMPLOYEES"."HIRE_DATE"="EMPLOYEES"."HIRE_DATE")
io = NOCOST, cpu = 50.000000, sel = 0.056604 flag = 2064 ("EMPLOYEES"."DEPARTMENT_ID"=30)
Total Scan IO Cost = 1.373832 (scan (Disk))
                     + 0.000000 (io filter eval) (= 0.000000 (per row) * 107.000000 (#rows))
                     = 1.373832
Total Scan CPU Cost = 73057.200000 (scan (Disk))
                      + 5404.591837 (cpu filter eval) (= 50.510204 (per row) * 107.000000 (#rows))
                      = 78461.791837
Inner table: EMPLOYEES Alias: EMPLOYEES
Access Path: TableScan
NL Join: Cost: 150.241105 Resp: 150.241105 Degree: 1
Cost_io: 150.000000 Cost_cpu: 8457769
Resp_io: 150.000000 Resp_cpu: 8457769
:
(中略)
:
Final cost for query block SEL$5DA710D3 (#1) - All Rows Plan:
Best join order: 1
Cost: 5.019856 Degree: 1 Card: 7.000000 Bytes: 238.000000
Resc: 5.019856 Resc_io: 5.000000 Resc_cpu: 696550
Resp: 5.019856 Resp_io: 5.000000 Resc_cpu: 696550
kkoqbc-subheap (delete addr=0x7fc0f549fb78, in-use=55888, alloc=65704)
kkoqbc-end:
:
call(in-use=17592, alloc=98600), compile(in-use=202344, alloc=202840), execution(in-use=3200, alloc=4032)

Best join order:選択された結合順序
Cost:選択された実行計画のコスト
Degree:並列度
Card:最終的に処理される行数(カーディナリティ)の見積もり
Resc:シリアル実行のコスト
Resp:パラレル実行のコスト

最終的に選択された実行計画

============
Plan Table
============
------------------------------------------------------------------+-----------------------------------+
| Id | Operation                              | Name             | Rows | Bytes | Cost | Time      |
------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                       |                  |       |        |    5 | |
| 1   | HASH JOIN SEMI                        |                  |     7 |    238 |    5 | 00:00:01 |
| 2   |   NESTED LOOPS SEMI    |    |     7 |    238 |    5 | 00:00:01 |
| 3   |    STATISTICS COLLECTOR                |                  |       |        |      | |
| 4   |     TABLE ACCESS FULL                  | EMPLOYEES        |   107 |   2461 |    3 | 00:00:01 |
| 5   |    TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES        |     1 |     11 |    2 | 00:00:01 |
| 6   |     INDEX RANGE SCAN                   | EMP_DEPARTMENT_IX|     6 |    |    1 | 00:00:01 |
| 7   |    TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES        |     6 |     66 |    2 | 00:00:01 |
| 8   |     INDEX RANGE SCAN                   | EMP_DEPARTMENT_IX|     6 |        |    1 | 00:00:01 |
------------------------------------------------------------------+-----------------------------------+

参考資料

Cost-Based Oracle Fundamentals

Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

作者: Jonathan Lewis
出版社/メーカー: Apress
発売日: 2007/01/24
メディア: ペーパーバック
クリック: 1回
この商品を含むブログ (1件) を見る

Secrets of the Oracle Database

Secrets of the Oracle Database (Expert's Voice in Oracle)

作者: Norbert Debes
出版社/メーカー: Apress
発売日: 2009/06/02
メディア: ペーパーバック
この商品を含むブログを見る

Expert Oracle Practices

Expert Oracle Practices: Oracle Database Administration from the Oak Table

作者: Pete Finnigan,Alex Gorbachev,Tim Gorman,Charles Hooper,Jonathan Lewis,Niall Litchfield,Robyn Sands,Joze Senegacnik,Riyaj Shamsudeen,Jeremiah Wilton,Graham Wood,Connie Green,Karen Morton,Randolf Geist,Uri Shaft,Melanie Caffrey,Andrew Morton,Tom Green,Paul Wilton,Joe Lewis
出版社/メーカー: Apress
発売日: 2010/01/20
メディア: ペーパーバック
この商品を含むブログを見る

Oracle SQL Tuning with Oracle SQLTXPLAIN: Oracle Database 12c Edition, Second Edition