解析10053事件

        
你是否想知道一句sql語句如何執行，它是否走索引，是否採用不同得驅動表，是否用nestloop join,hash join…..?這一切對你是否很神秘呢？或許你會說execution plan能看到這些東西，但是你是否清楚execution plan是如何得到？這篇文章就是給出了隱藏在execution plan底下的具體實現。

10053事件
10053事件是oracle提供的用於跟蹤sql語句成本計算的內部事件，它能記載CBO模式下oracle最佳化器如何計算sql成本，生成相應的執行計劃。

如何設定10053事件
設定本session的10053
開啟：
Alter session set events '10053 trace name context forever[,level {1/2}]';
關閉：

Alter session set events '10053 trace name context off';;

設定其他session的10053
開啟：
SYS.DBMS_SYSTEM.SET_EV (, , 10053, {1|2}, '')
關閉：
SYS.DBMS_SYSTEM.SET_EV (, , 10053,0, '')

跟其他跟蹤事件不同，10053提供了兩個跟蹤級別，但是級別2的跟蹤資訊比級別1少（其他跟蹤事件如10046跟蹤級別越高資訊越多），跟蹤資訊將被記錄到user_dump_dest目錄底下。注意，要實現跟蹤必須滿足兩個條件：sql語句必須被hard parse並且必須使用CBO最佳化器模式。如果sql語句已經被parse過，那麼10053不生成跟蹤資訊。如果你使用RULE最佳化器，那麼10053也不會生成跟蹤資訊。


跟蹤內容
跟蹤檔案包括6部分：
Sql語句
最佳化器相關引數
基本統計資訊
基本表訪問成本
綜合計劃
特殊功能的成本重計算

這篇文章將會涉及到前4項和一部分第5項的內容，我們將會用以下語句作為例子：

select dname, ename from emp, dept
where emp.deptno = dept.deptno
and ename = :b1




sql語句：
這部分是整個跟蹤檔案裡最容易理解的部分，包括了所執行的sql語句，如果你採用RULE模式最佳化器，那麼除了這一部分外將不會有多餘資訊出現在跟蹤檔案裡。











最佳化器相關引數：
記載了所有影響成本計算的引數

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 8.1.6
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 131072
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = FALSE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = FALSE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 32
SORT_AREA_SIZE = 131072



基本統計資訊:
下一部分是所有表和索引的基本統計資訊
基本統計資訊包括

表:
Trace label       dba_tables column
CDN                   NUM_ROWS                       表記錄數
NBLKS                BLOCKS                         高水位以下的block數
TABLE_SCAN_CST                                        全表掃描的I/O成本
AVG_ROW_LEN    AVG_ROW_LEN                       平均行長

索引:
Trace label       dba_indexes column
Index#, col#                                      索引號及表列號
LVLS                BLEVEL                             BTREE索引高度
#LB                    LEAF_BLOCKS                   索引葉塊數
#DK                DISTINCT_KEYS                   不重複索引關鍵字
LB/K              AVG_LEAF_BLOCKS_PER_KEY       葉塊/關鍵字
DB/K          AVG_DATA_BLOCKS_PER_KEY       資料塊/關鍵字
CLUF              CLUSTERING_FACTOR                    索引聚合因子


***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: DEPT Alias: DEPT
TOTAL :: CDN: 16 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 20
-- Index stats
INDEX#: 23577 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 16 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: EMP Alias: EMP
TOTAL :: CDN: 7213 NBLKS: 85 TABLE_SCAN_CST: 6 AVG_ROW_LEN: 36
-- Index stats
INDEX#: 23574 COL#: 1
TOTAL :: LVLS: 1 #LB: 35 #DK: 7213 LB/K: 1 DB/K: 1 CLUF: 4125
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534
INDEX#: 23576 COL#: 8
TOTAL :: LVLS: 1 #LB: 46 #DK: 12 LB/K: 3 DB/K: 34 CLUF: 418
***************************************






基本表訪問成本：
這裡開始CBO將會計算單表訪問的成本

單表訪問路徑
SINGLE TABLE ACCESS PATH .........................................................................................................................................1
Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2
NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3
TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4
Access path: tsc Resc: 6 Resp: 6............................................................................................................5
Access path: index (equal) ...............................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9

我們看一下上面是什麼意思。首先CBO列出了ename列的統計資訊(第2，3行),這些統計資訊來自dba_tab_columns。
列的統計資訊和dba_tab_columns中對應的列名如下
Trace label             dba_tables column
NDV                   NUM_DISTINCT     列的不重複值數
NULLS                NUM_NULLS        列的空行數
DENS                    DENSITY           列密度，沒有直方圖的情況下= 1/NDV
LO                       LOW_VALUE        列的最小值 (只對數字列)
HI                      HIGH_VALUE    列的最大值 (只對數字列)
第4行出現了表的行數ORIG CDN和計算過的行數 CMPTD CDN (computed cardinality). 計算公司如下，
CMPTD CDN = ORIG CDN * FF
在這裡 FF 表示過濾因子（Filter Factor）。我們稍後再來看FF是什麼及如何計算的。
第5行表示了全表掃描的成本。 這裡的成本是62, 是由NBLKS和db_file_multi_block_read_count初始化引數計算出來的。.
第6－8行是索引訪問的成本。
第9行是總結了以上資訊並選出了最優的訪問路徑為全表掃描，成本為6。





表掃描成本
讓我們來看一下全表掃描成本(tsc)是如何計算的 這裡有其他兩個大表的基本統計資訊。
TOTAL :: CDN: 115630 NBLKS: 4339 TABLE_SCAN_CST: 265 AVG_ROW_LEN: 272
TOTAL :: CDN: 454503 NBLKS: 8975 TABLE_SCAN_CST: 548 AVG_ROW_LEN: 151
你可能曾經看到過全表掃描成本= 訪問的塊數目/ db_file_multi_block_read_count. 看起來這個等式很有意義因為oracle在做全表掃描時每個I/O請求將會讀取db_file_multi_block_read_count個塊。但是，我們計算以上統計資訊得到
NBLKS / TABLE_SCAN_CST = 4339 / 265 = 16.373 ≠ db_file_multi_block_read_count(這裡的值是32，可以看前面引數那一頁)
另外一個表為
NBLKS / TABLE_SCAN_CST = 8975 / 548 = 16.377


全表掃描成本和db_file_multi_block_read_count

CBO將會根據NBLKS和db_file_multiblock_read_count來估計全表掃描成本，但是db_file_multiblock_read_count通常會被打上折扣。實際上我們可以認為等式會是
TABLE_SCAN_CST = NBLKS / k
我們來看一下k和db_file_multiblock_read_count 究竟有什麼規律可尋。我們來做一個實驗，使用不同的
db_file_multiblock_read_count值4, 6,8, 12,16, 24,32來測試。

圖請見WORD版本

橫軸為db_file_multiblock_read_count，縱軸為K。
注意引數K僅僅用在全表掃描或快速索引掃描上，實際的I/O成本還與其他因數有關，比如說需要訪問的表已經在記憶體中的塊及塊的數量。




過濾因子(FF)
為了理解索引訪問成本我們需要了解一下過濾因子。 過濾因子是一個介於0和1之間的數字，反映了記錄的可選擇性。如果一個列有10種不同的值，我們需要查詢等於其中某一個值的記錄時，如果這10種值平均分佈的話，你將得到1/10的行數。如果沒有直方圖，過濾因子為FF = 1/NDV = density

再來看一下過濾因子和查詢條件的關係
不使用繫結變數的情況:
predicate Filter factor
c1 = value 1/c1.num_distinct4
c1 like value 1/c1.num_distinct
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 2 * 1/c1.num_distinct
使用繫結變數的情況（8i）:
predicate Filter factor
col1 = :b1 col1.density
col1 {like | > | >= | < | <=} :b1 {5.0000e-02 | col1.
col1 between :b1 and :b2 5.0000e-02 * 5.0000e-
包含and和or的情況:
predicate Filter factor
predicate 1 and predicate 2 FF1 * FF2
predicate 1 or predicate 2 FF1 + FF2 – FF1 * FF2


包含直方圖的列：
如果一個列包含了直方圖資訊，那麼它的density就來自於直方圖。關於直方圖的內容請參考官方手冊，這裡不在細述。由於直方圖的存在FF並不是簡單的等於1/NDV,而是來自於直方圖中各個列的density，所有有直方圖的話CBO將可能採取不一樣的執行路徑。













索引訪問成本：
現在我們知道了聚合因子的概念，我們再來看一看索引訪問的成本
SINGLE TABLE ACCESS PATH .........................................................................................................................................1
Column: ENAME Col#: 2 Table: EMP Alias: EMP.....................................................................2
NDV: 42 NULLS: 0 DENS: 2.3810e-002 ...........................................................................3
TABLE: EMP ORIG CDN: 7213 CMPTD CDN: 172 ........................................................................................4
Access path: tsc Resc: 6 Resp: 6............................................................................................................5
Access path: index (equal) ...............................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
BEST_CST: 6.00 PATH: 2 Degree: 1..............................................................................................................9

我們來看6-8行，這裡表示了索引訪問的成本。第6行表示這裡採取索引equal的方法來訪問，再來回憶一下索引的基本統計資訊
INDEX#: 23575 COL#: 2
TOTAL :: LVLS: 1 #LB: 48 #DK: 42 LB/K: 1 DB/K: 36 CLUF: 1534

根據索引成本計算公式
blevel + FF*leaf_blocks + FF*clustering_factor
1 + 2.3810e-002-2*48 + 2.3810e-002-2*1534 = 1 + 1.1429 + 36.5245 = 38.6674
這裡的FF就等於TBSEL=DENS=2.3810e-002,由於我們的查詢條件為ename = :b1所以得出FF為ENAME列的DENS，
其實索引訪問方式的成本計算公式
• Unique scan blevel+1
• Fast full scan leaf_blocks / k ( k = 1.6765x0.6581 )
• Index-only blevel + FF*leaf_blocks


讓我們用別的例子證明一下索引成本計算，語句為
select … from tbl a
where a.col#1 = :b1
and a.col#12 = :b2
and a.col#8 = :b3

索引和列的基本統計資料如下
INDEX#     COL#    LVLS    #LB        #DK     LB/K    DB/K    CLUF
8417       27,1    1    13100     66500     1        22     1469200
8418    1,12,7    2    19000 74700     1        15     1176500
8419    3,1,4,2     2    31000 49700     1       2     118000
15755    1,12,8    1    12600 18800     1        30     1890275
8416 1,2,33,4,5,6 2        25800 1890300 1       1    83900
Col#: 1 NDV: 10 NULLS: 0 DENS: 1.0000e-001-1
Col#: 12 NDV: 8 NULLS: 0 DENS: 1.2500e-001
Col#: 8 NDV: 33 NULLS: 0 DENS: 3.0303e-001

Access path: index (scan)...................................................................................................................................1
INDEX#: 8418 CST: 14947 IXSEL: 1.2500e-002 TBSEL: 1.2500e-002 ........................................2
Access path: index (equal) ...............................................................................................................................3
INDEX#: 15755 CST: 7209 IXSEL: 0.0000e+000 TBSEL: 3.7879e-003 ......................................4
Access path: index (scan) .................................................................................................................................5
INDEX#: 8416 CST: 10972 IXSEL: 1.0000e-001 TBSEL: 1.0000e-001 ........................................6
5個索引中，索引(#8417 and #8419) 將不會被考慮因為他們的首列不出現在查詢條件中。.

INDEX# 8418
索引包含的3個列中只有2列出現在查詢條件中，所以只用2列的DENS來計算過濾因子
FF = 1.0000e-001 * 1.2500e-001= 1.2500e-002
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 19,000*1.2500e-002 + 1176500*1.2500e-002
= 2 + 237.5 + 14706.25 = 14945.75
INDEX# 15755
索引包含的3列都出現在查詢條件中，用3列的DENS計算過濾因子
FF = 1.0000e-001 * 1.2500e-001 * 3.0303e-001 = 3.7879e-003
cost = lvl + FF*#LB + FF*clustering factor
= 1 + 12,600*3.7879e-003 + 1,890,275*3.7879e-003
= 2 + 47.73 + 7160.13 = 7208.86
INDEX# 8416
索引包含的3個列中只有1列出現在查詢條件中，所以只用1列的DENS來計算過濾因子
FF = 1.0000e-001
cost = lvl + FF*#LB + FF*clustering factor
= 2 + 25,800*1.0000e-001+ 83,900*1.0000e-001
= 2 + 2580 + 8390 = 10972
雖然索引8416只有一列出現在查詢條件中，但是它的成本還是低於索引8418，因為它的聚合因子(clustering factor)比較低，所以統計出來成本也比較低。關於聚合因子可以參考oracle官方文件。








綜合計劃：
這一部分開始是10053最大的一部分，在這裡CBO會評估各種JOIN方式及順序的成本。

1. NL - NESTED LOOP JOIN
join cost = cost of accessing outer table
+ (row number of outer table * cost of accessing inner table )
2. SM – SORT MERGE JOIN
join cost = (cost of accessing outer table + outer sort cost)
+ (cost of accessing inner table + inner sort cost)
3. HA – HASH JOIN
join cost = (cost of accessing outer table)
+ (cost of building hash table)
+ (cost of accessing inner table )


JOIN ORDER [N]

Join order[1]: DEPT [DEPT] EMP [EMP]
Now joining: EMP [EMP] *******
JOINS – NL
NL Join ..............................................................................................................................................................................1
Outer table: cost: 1 cdn: 16 rcz: 13 resp: 1..................................................................................2
Inner table: EMP ......................................................................................................................................................3
Access path: tsc Resc: 6 ...............................................................................................................................4
Join resc: 97 Resp: 97 ...............................................................................................................................5
Access path: index (join stp) ...........................................................................................................................6
INDEX#: 23575 TABLE: EMP ...........................................................................................................................7
CST: 39 IXSEL: 0.0000e+000 TBSEL: 2.3810e-002.......................................................................8
Join resc: 625 resp:625 .............................................................................................................................9
Access path: index (join index).....................................................................................................................10
INDEX#: 23576 TABLE: EMP .........................................................................................................................11
CST: 37 IXSEL: 0.0000e+000 TBSEL: 8.3333e-002.....................................................................12
Join resc: 593 resp:593 ...........................................................................................................................13
Access path: and-equal...................................................................................................................................14
CST: 19 ...............................................................................................................................................................15
Join resc: 305 resp:305 ...........................................................................................................................16
Join cardinality: 172 = outer (16) * inner (172) * sel (6.2500e-002) [flag=0].................17
Best NL cost: 97 resp: 97...............................................................................................................................18
第1行為JOIN方式
第2行為驅動表的成本，行數，行大小。這裡的行數為16，平均行長原本為20，但是因為DEPT表包含(DEPTNO, DEPT, and LOC)3列但僅有DEPTNO,DEPT等2列需要被join，所以計算後平均行長為16，所以在這裡也被稱為low row size.
第3行到16行透過NL JOIN的成本計算公式，計算出幾種不同join方法的成本。
1. Tablescan of EMP at a cost of 6:
cost = cost of outer + cardinality of outer * cost of inner = 1 + 16 * 6 = 97 lines 3 to 5
2. Scan of index 23575 on ENAME at a cost of 39:
cost = 1 + 16 * 39 = 625 lines 6 to 9
3. Scan of index 23576 on DEPTNO at a cost of 37:
cost = 1 + 16 * 37 = 593 lines 10 to 13
4. An “and-equal” access at a cost of 19:
cost = 1 + 16 * 19 = 305 lines 14 to 16
第17行CBO估算出這個JOIN結果集的記錄數，它將被最為下一次join的輸入。它的計算公式為
Join cardinality:= outer   * inner   * join selectivity
而join selectivity為
join selectivity = 1/max[ NDV(t1.c1), NDV(t2.c2) ]
* [ (card t1 - # t1.c1 NULLs) / card t1 ]
* [ (card t2 - # t2.c2 NULLs) / card t2 ]
Join cardinality只會被用於NL JOIN中，其他JOIN會採取不同辦法。
最後在18行，CBO將會列出成本最低的NL JOIN的方法。

JOINS - SM
SM Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 25 Rows: 16
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 23577 TABLE: DEPT
CST: 2 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 2 cdn: 16 rcz: 13 deg: 1 resp: 2
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
SORT resource Sort statistics
Sort width: 3 Area size: 43008 Degree: 1
Blocks to Sort: 1 Row size: 20 Rows: 172
Initial runs: 1 Merge passes: 1 Cost / pass: 2
Total sort cost: 2
Merge join Cost: 10 Resp: 10
在SM JOIN中成本為
Cost of outer + cost of inner + sort cost for outer + sort cost for inner = 1+ 6 + 2 + 2 = 11.
在這裡CBO減去1所以最終等於10。在第2個SM JOIN的方法下透過了已經排序的索引，所以成本為 2 + 6 + 0 (no sort on outer) + 2 = 10.

JOINS – HA
HA Join
Outer table:
resc: 1 cdn: 16 rcz: 13 deg: 1 resp: 1
Inner table: EMP
resc: 6 cdn: 172 rcz: 9 deg: 1 resp: 6
Hash join one ptn: 1 Deg: 1
hash_area: 32 buildfrag: 33 probefrag: 1 ppasses: 2
Hash join Resc: 8 Resp: 8
Join result: cost: 8 cdn: 172 rcz: 22
根據HA JOIN公式，計算出成本為
(cost of accessing outer table)+ (cost of building hash table)+ (cost of accessing inner table )
=1+6+1=8


所以在這裡HA JOIN會被選做最最佳化的執行路徑，SQL語句將會最終走HA JOIN.



多重JOIN:
如果出現大於兩個表進行JOIN的情況，那麼會有更多的join順序被考慮，4個表join的話會有24種join順序，5個表的話會有120個join順序，n個表會有n!個join順序。由於估算每種join順序都會耗費cpu，所以oracle用一個初始化引數optimizer_max_permutations來限制最大計算join順序。若想了解多重join的更多資訊，請搜尋相關sql調整的資料。


結論：
10053是一個很好的理解CBO工作機制的工具，如果輔以10046事件檢視執行計劃，那麼整個sql語句從解析到執行的過程都一目瞭然了。


reference:
A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT
Wolfgang Breitling, Centrex Consulting Corporation