首先介紹與CBO計(jì)算成本有關(guān)的一些參數(shù)說明，介紹了CBO在查詢中如何計(jì)算成本。接著給出10053跟蹤文件分析的一般方法，最后結(jié)合一個(gè)具體跟蹤文件，對(duì)如何分析10053跟蹤文件進(jìn)行詳細(xì)闡述。
1. 關(guān)于Oracle案例學(xué)習(xí)
Oracle案例學(xué)習(xí)主要是作為一種工具，主要提供對(duì)復(fù)雜事件、進(jìn)程、過程以及一系列相關(guān)事件的信息與知識(shí)。每個(gè)案例都是在處理實(shí)際問題的經(jīng)驗(yàn)基礎(chǔ)上編寫的。
每個(gè)學(xué)習(xí)案例包含一定的技能級(jí)別。技能級(jí)別指文檔的閱讀者在學(xué)習(xí)此案例之前，應(yīng)該具備什么樣的技能級(jí)別。
有以下幾個(gè)級(jí)別：
專家級(jí)：在相關(guān)主題領(lǐng)域有豐富經(jīng)驗(yàn)
中級(jí)：在相關(guān)主題領(lǐng)域有一定的經(jīng)驗(yàn)
入門級(jí)：在相關(guān)主題領(lǐng)域沒什么經(jīng)驗(yàn)
本案例建議的級(jí)別：專家級(jí)
2. 案例學(xué)習(xí)摘要
本案例使用跟蹤文件，闡述分析10053跟蹤文件的方法體系。注意，10053事件跟蹤文件主要用于輔助oracle開發(fā)者以及技術(shù)支持來診斷優(yōu)化器相關(guān)的問題，它隨著新版本或者補(bǔ)丁集而有所變化。本文的主要目的不是對(duì)10053跟蹤文件提供全面的參考，而是介紹oracle工程師怎樣使用這種跟蹤文件。
同時(shí)，我們將窺探CBO在查詢中如何計(jì)算成本，以及CBO是如何最終獲得執(zhí)行計(jì)劃的。
需要指出的是，CBO在估算成本的時(shí)候，會(huì)隨著版本的變化，其算法會(huì)有不同。
這里我們將會(huì)分析一個(gè)糟糕的執(zhí)行計(jì)劃，并判斷出CBO是如何計(jì)算成本并導(dǎo)致不好的計(jì)劃的。我們會(huì)在不同地方比較10053跟蹤，但主要精力還是放在這個(gè)糟糕的計(jì)劃是如何計(jì)算成本的。良好的執(zhí)行計(jì)劃一般而言比不好的執(zhí)行計(jì)劃(其中沒有考慮使用索引)更簡(jiǎn)短。
檢查10053的原因一般是為了搞清楚CBO為什么這樣進(jìn)行選擇。10053可以幫我們回答諸如“索引為什么沒被使用”之類的問題。“CBO為什么要選擇全表掃描?” 。一般來說，10053不是處理性能問題時(shí)最先使用的方法---在這方面，查看執(zhí)行計(jì)劃并使用tkprof工具能更好的獲取信息。10053用于深入CBO進(jìn)行選擇的原因分析。
3. 案例歷史
執(zhí)行一個(gè)未使用提示的sql語(yǔ)句(一個(gè)包含3種連接的select語(yǔ)句)需要9個(gè)小時(shí)，而如果使用"NO_INDEX"提示，將會(huì)在4分鐘內(nèi)執(zhí)行完。表使用的是分區(qū)表，而且用到了并行查詢。另外，用戶將"OPTIMIZER_INDEX_CACHING"設(shè)置為70(不知用戶為什么這么做，我們猜測(cè)可能是因?yàn)楫?dāng)時(shí)他們并沒有得到想要的執(zhí)行計(jì)劃才這么做)。此參數(shù)設(shè)置效果可以使單塊索引I/O下降70%。
使用10053跟蹤，獲取未加提示(“不好的”)的執(zhí)行計(jì)劃與加提示(“好的”)的執(zhí)行計(jì)劃。兩個(gè)執(zhí)行計(jì)劃的主要不同點(diǎn)是，未加提示的執(zhí)行計(jì)劃使用nested嵌套循環(huán)連接方式，內(nèi)層連接使用的是INDEX FULL SCAN(不是index fast full scan)操作來作為內(nèi)層行集。加提示的執(zhí)行計(jì)劃使用哈希連接，INDEX FAST FULL SCAN (IFF)操作的結(jié)果集作為內(nèi)層行集。
注意，就本案例而言，新版本中使用10053產(chǎn)生的跟蹤文件可能會(huì)發(fā)生很大變化。
一般而言，新版本的跟蹤文件比較容易閱讀，在"預(yù)分析工作"部分需要做的工作較少。
4. 預(yù)分析工作
在開始分析跟蹤文件前，我們首先進(jìn)行一些觀測(cè)，了解一些CBO計(jì)算成本時(shí)的有關(guān)因素。有時(shí)，這些參數(shù)和因素的值會(huì)很好的告訴我們，為什么從諸多執(zhí)行計(jì)劃中選擇了特定的執(zhí)行計(jì)劃。
要收集10053事件的跟蹤文件，可以在sqlplus中使用下面的語(yǔ)法命令：
SQL> connect / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug event 10053 trace name context forever, level 1
SQL> ...enter your query here...
SQL> oradebug event 10053 trace name context off
SQL> oradebug tracefile_name
/chia/web/admin/PTAV3/udump/ptav3_ora_15365.trc
"oradebug tracefile_name"會(huì)顯示10053產(chǎn)生的跟蹤路徑與文件名。
A) 確認(rèn)查詢被跟蹤了
這步很重要，因?yàn)槲覀兿氪_認(rèn)所跟蹤的是相關(guān)查詢的跟蹤信息。在跟蹤文件的QUERY部分找到sql語(yǔ)句，并確認(rèn)該sql就是我們所關(guān)心的sql語(yǔ)句。在10g版本中，如果沒有使用綁定變量，QUERY部分在跟蹤文件的結(jié)尾，否則，QUERY部分就在跟蹤文件的開始。注意，搞清楚我們關(guān)心的QUERY部分跟哪些跟蹤信息關(guān)聯(lián)。有時(shí)候，很容易誤以為跟蹤文件尾部的QUERY部分就是想要跟蹤的信息(這在10g中沒有使用綁定變量的sql語(yǔ)句中很容易發(fā)生)。
B) 參數(shù)
OPTIMIZER_FEATURES_ENABLE = 9.2.0
_OPTIMIZER_PERCENT_PARALLEL = 101
OPTIMIZER_INDEX_CACHING = 70
此參數(shù)會(huì)影響索引訪問的成本，使用索引的成本為原始成本乘以(100 - optimizer_index_caching)/100。所以，本案例中，會(huì)用以下的因子相乘，來減少索引使用的成本：(100 - 70)/100 = 0.3或者大約1/3。這就是說，索引成本乘以0.3，即為不使用此參數(shù)情況下成本的1/3。注意，索引I/O成本根據(jù)"BLEVEL", "LEAF_BLOCKS", 以及 "CLUF" (群集因子)的值來計(jì)算。這個(gè)參數(shù)只影響與BLEVEL和LEAF_BLOCKS有關(guān)的成本部分。CLUF影響對(duì)表訪問的成本，參數(shù)OPTIMIZER_INDEX_CACHING對(duì)其不會(huì)有影響。
OPTIMIZER_INDEX_COST_ADJ = 99
此參數(shù)用下面的分?jǐn)?shù)來表示索引訪問成本的百分比：optmizer_index_cost_adj / 100。本案例中，該分?jǐn)?shù)為99/100 或者 0.99。該參數(shù)會(huì)影響所有的索引成本，即使在連接中使用的索引也一樣受影響。
OPTIMIZER_DYNAMIC_SAMPLING = 1
該參數(shù)控制CBO多大程度的依賴于動(dòng)態(tài)樣本以便獲取集的勢(shì)和選擇率的信息，集的勢(shì)和選擇率會(huì)在計(jì)算訪問路徑的成本時(shí)用到。如果設(shè)置為1，表示僅僅當(dāng)查詢中表的統(tǒng)計(jì)信息缺失時(shí)才會(huì)使用樣本統(tǒng)計(jì)方法。
_OPTIMIZER_COST_MODEL = CHOOSE
如果設(shè)置為CHOOSE，而且已收集系統(tǒng)的統(tǒng)計(jì)信息，CBO將使用新的CPU模型。如果設(shè)置為I/O，將會(huì)使用舊成本模型，忽略CPU成本。
DB_FILE_MULTIBLOCK_READ_COUNT = 64
該參數(shù)控制執(zhí)行全表掃描或者索引掃描時(shí)的成本。該參數(shù)的值越高，執(zhí)行全表掃描或者索引掃描時(shí)成本越低。該參數(shù)的值被CBO要么按照固定公式(如果OPTIMIZER_COST_MODEL = io)計(jì)算，要么從收集的實(shí)際統(tǒng)計(jì)信息中計(jì)算并進(jìn)行參考。
_CPU_TO_IO = 0 (默認(rèn))
該參數(shù)用于量度在使用CPU和I/O成本來計(jì)算總成本時(shí)，一次I/O成本需要的CPU周期。如果設(shè)置為0，即默認(rèn)值，CBO要么使用一個(gè)內(nèi)部固定的值，要么使用系統(tǒng)統(tǒng)計(jì)信息派生的一個(gè)值(統(tǒng)計(jì)信息包含CPU轉(zhuǎn)速，單塊I/O時(shí)間，多塊I/O時(shí)間，多塊I/O時(shí)所讀的平均塊數(shù)目)。當(dāng)CBO考慮CPU成本時(shí)，為了確認(rèn)成本，判斷CBO使用該參數(shù)的什么值非常重要。
C) 計(jì)算CBO使用的CPU對(duì)I/O比率
為了確定CBO用到的_CPU_TO_IO的值，在10053中，必須找到一個(gè)入口，此入口包含下述參數(shù)的值：CPU 成本, I/O 成本, 總成本。通過獲取這三個(gè)值，使用相關(guān)的公式，我們可以計(jì)算CBO內(nèi)部使用的這個(gè)比率。
_CPU_TO_IO的值在10053中是個(gè)常量，在任何計(jì)算中都是一樣的，因此任何入口中顯示的這三個(gè)變量的值，對(duì)于計(jì)算_CPU_TO_IO的值都是有用的。需要注意的是，選擇一個(gè)CPU和I/O成本較大的值，因?yàn)樗鼈兊闹翟酱?，算出來的結(jié)果就越準(zhǔn)確。
找一個(gè)index fast full (IFF)掃描，或者一些包含CPU，IO與總體成本有關(guān)的信息。
Access path: index (iff)
Index: PK_CIPBF_IX
TABLE: CERT_INSURED_PLAN_BENEFIT_FACT
RSC_CPU: 2865229980 RSC_IO: 52693
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 55630 Resp: 27815
使用公式
綜合的Resc Cost = (RSC_CPU cost / _cpu_to_io) + RSC_IO Cost
計(jì)算 _cpu_to_io:
_cpu_to_io = RSC_CPU Cost / (Combined Cost - RSC_IO Cost)
= 2865229980 / (55630 - 52693)
= 975563.49 CPU cycles per IO
D)計(jì)算多塊讀除數(shù)
當(dāng)CBO估算全表掃描或者索引快速全掃描的成本時(shí)，它會(huì)使用除數(shù)去除表或者索引的總塊數(shù)，這個(gè)除數(shù)估算了每次從磁盤上進(jìn)行的物理讀時(shí)將要讀入的塊數(shù)(這里稱為多塊讀除數(shù)MBDivisor)。以前，參數(shù)"DB_FILE_MULTIBLOCK_READ_COUNT”是估算多塊讀除數(shù)的一個(gè)基數(shù)(為了補(bǔ)償實(shí)際環(huán)境下的限制，它的值通過公式進(jìn)行減少的計(jì)算)，在9.2及其以后的版本中，多塊讀除數(shù)的值的估算在統(tǒng)計(jì)信息收集了的情況下，有很大的變化。
在分析10053之前，計(jì)算出CBO使用的這個(gè)除數(shù)，能幫助我們快速了解它的值是不是合理的。較低的值會(huì)導(dǎo)致CBO使用FTS和IFF時(shí)候的成本較索引訪問路徑要昂貴，較高的值，會(huì)使FTS和IFF的成本較低廉。
為了獲得這個(gè)除數(shù)，找一個(gè)簡(jiǎn)單的表訪問路徑入口，獲取表掃描("tsc")的資源成本("Resc")。然后查找表包含的總塊數(shù)。計(jì)算公式如下：
tsc cost = Num Blocks / MBDivisor
MBdivisor = Num Blocks / tsc cost
例如：
在"Base Statistical Information"部分:
Table stats Table: CERTIFICATE Alias: A12
PARTITION [95] CDN: 3164453 NBLKS: 125272 AVG_ROW_LEN: 617
TOTAL :: CDN: 3164453 NBLKS: 125272 AVG_ROW_LEN: 617
在"Single Table Access Path"部分:
SINGLE TABLE ACCESS PATH
...
TABLE: CERTIFICATE ORIG CDN: 3164453 ROUNDED CDN: 2089 CMPTD CDN: 2089
Access path: tsc Resc: 116982 Resp: 29052
Mdivisor = Nblks / tsc
Mdivisor = 125272 / 116982 = 1.07
注意：這個(gè)值有點(diǎn)低。查看系統(tǒng)統(tǒng)計(jì)信息以及了解這個(gè)值是如何計(jì)算的，這非常有用。較低的值會(huì)使FTS與IFF相對(duì)索引掃描來說較昂貴。也許這些值是真實(shí)的，但也可能系統(tǒng)統(tǒng)計(jì)信息是在過去某個(gè)時(shí)間獲取過，但是不能反應(yīng)系統(tǒng)負(fù)荷的實(shí)際狀況?？紤]FTS可能較昂貴，客戶可能覺得他們進(jìn)一步要使用index_cost_adjustment參數(shù)來降低索引成本。很容易看出，這個(gè)數(shù)據(jù)庫(kù)在很多情況下，將傾向于使用一種索引訪問的方式而不是FTS(全表掃描)。
多塊讀除數(shù)已經(jīng)很低，這很可能阻礙CBO選擇非索引的訪問路徑，這種情況下，客戶將OPTIMIZER_INDEX_COST_ADJ設(shè)置得如此的高，看起來有點(diǎn)不尋常。了解一下客戶以前為什么要設(shè)置這個(gè)值會(huì)很有幫助。
E)查看" BASE STATISTICAL INFORMATION"與" SINGLE TABLE ACCESS PATH"部分，檢查缺失或者不充分的統(tǒng)計(jì)信息：
典型問題包括：
表或者索引沒有統(tǒng)計(jì)信息
如果一個(gè)對(duì)象沒有收集統(tǒng)計(jì)信息，你會(huì)看到一些消息，比如："(NOT ANALYZED)"。
但是對(duì)于索引，不會(huì)有消息明確的指出它們沒有被分析過。因此，你只能讀索引的統(tǒng)計(jì)信息，并查看默認(rèn)統(tǒng)計(jì)信息。LEAF_BLOCKS默認(rèn)統(tǒng)計(jì)信息是25，CLUSTERING_FACTOR是800。
對(duì)于分區(qū)對(duì)象，需知道是否有全局或者分區(qū)級(jí)別統(tǒng)計(jì)信息被收集了。
為了檢測(cè)global only(非分區(qū)級(jí)別統(tǒng)計(jì)信息被收集)，查找UNANALYZED分區(qū)。例如(下述數(shù)據(jù)不是來自當(dāng)前實(shí)例的跟蹤信息)：
Table stats Table: SALES Alias: SALES
(Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: CDN: 919315 NBLKS: 1768 AVG_ROW_LEN: 29
PARTITIONS::
PRUNED: 5
ANALYZED: 0 UNANALYZED: 5
TOTAL :: CDN: 919315 NBLKS: 94 AVG_ROW_LEN: 29
不幸的是，沒辦法弄清楚系統(tǒng)的總體統(tǒng)計(jì)信息的收集，是采用全局樣本還是單個(gè)的分區(qū)表的方式。如果有疑問，全局樣本最好是查看DBA_TABLES.GLOBAL_STATS，如果為YES，則表示全局統(tǒng)計(jì)信息已被收集。
無(wú)直方圖
為特定的列查找"No Histogram". 例子 (下述數(shù)據(jù)不是當(dāng)前實(shí)例的追蹤文件):
SINGLE TABLE ACCESS PATH
COLUMN: TIME_ID(DATE) Col#: 3 Table: SALES Alias: SALES
Size: 8 NDV: 1187 Nulls: 0 Density: 8.4246e-04 Min: 2450815 Max: 2452275
No Histogram: #BKT: 1
(1 uncompressed buckets and 2 endpoint values)
分析10053的過程，一般是從跟蹤文件的底部往上看，再到感興趣的區(qū)域。感興趣的區(qū)域就是那些屬于看起來有問題的執(zhí)行計(jì)劃的區(qū)域。
在這里列舉進(jìn)行10053跟蹤文件分析的步驟：
1) 從文件末尾開始
確認(rèn)你感興趣的sql語(yǔ)句存在于跟蹤文件中。如果跟蹤文件中有EXPLAIN PLAN，編輯一下跟蹤文件盡量便于閱讀。
2)查看所選執(zhí)行計(jì)劃的最終成本。
3)查看產(chǎn)生最終成本的連接順序
4)查看感興趣的引起成本的連接順序部分
5)查看連接順序產(chǎn)生成本的連接類型
6)檢查在步驟5中找到的連接類型的成本
a)確認(rèn)用到何種訪問路徑
b)檢查其他被拒絕的訪問路徑(僅適用于嵌套循環(huán)連接，在對(duì)內(nèi)部行集的訪問路徑中，有幾個(gè)訪問路徑產(chǎn)生成本)
詳細(xì)分析
1.從結(jié)尾開始，從執(zhí)行計(jì)劃開始
在10053中獲得準(zhǔn)確的執(zhí)行計(jì)劃輸出，很重要。有些信息在10053中會(huì)丟失，我們需要使用執(zhí)行計(jì)劃來得出成本計(jì)算是如何進(jìn)行的結(jié)論。有時(shí)候我們會(huì)使用執(zhí)行計(jì)劃來調(diào)查10053或者檢查我們的分析。
注意，根據(jù)parent ID來編排執(zhí)行計(jì)劃的步驟，使得執(zhí)行計(jì)劃的層次具有可讀性。
請(qǐng)注意，包含在跟蹤文件中的執(zhí)行計(jì)劃的輸出會(huì)隨著版本的變化而不同，而且經(jīng)常不會(huì)出現(xiàn)。在10g中，可以通過調(diào)整10053跟蹤信息，然后執(zhí)行一次EXPLAIN PLAN FOR....命令，來獲取一個(gè)格式化好了的執(zhí)行計(jì)劃。然而，綁定變量的出現(xiàn)可能會(huì)影響實(shí)際產(chǎn)生的執(zhí)行計(jì)劃，EXPLAIN PLAN命令發(fā)現(xiàn)不了這一點(diǎn)，因?yàn)椴煌慕壎ㄗ兞恐涤胁煌脑L問路徑。
2.查看執(zhí)行計(jì)劃的最終成本
Final:
CST: 20762 CDN: 1 RSC: 83447 RSP: 20762 BYTES: 173
IO-RSC: 20658 IO-RSP: 82626 CPU-RSC: 101017010 CPU-RSP: 801120184
PLAN
Cost of plan: 20762
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
SORT GROUP BY 1
TABLE ACCESS CERTIFICATE BY LOCAL INDEX R 2 1
NESTED LOOPS 3 2
NESTED LOOPS 4 3
TABLE ACCESS PREMIUM_PLAN_COD FULL 5 4
INDEX PK_CIPBF_IX FULL SCAN 6 4
INDEX XPKCERTIFICATE RANGE SCAN 7 3
好的執(zhí)行計(jì)劃 (在另外一個(gè)跟蹤文件中)，用到了NO_INDEX提示，看起來像下面這樣:
Cost of plan: 58201
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
SORT GROUP BY 1
TABLE ACCESS CERTIFICATE BY LOCAL INDEX R 2 1
NESTED LOOPS 3 2
HASH JOIN 4 3 <== hash join instead of NL
TABLE ACCESS PREMIUM_PLAN_COD FULL 5 4 <== full table scan
instead of index
full scan
TABLE ACCESS CERT_INSURED_PLA FULL 6 4
INDEX XPKCERTIFICATE RANGE SCAN 7 3
使用cost (20762)作為關(guān)鍵字，查找產(chǎn)生此成本的連接順序，我們找到如下：
Join result: cost: 20762 cdn: 1 rcz: 173
Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122
Best so far: TABLE#: 2 CST: 20266 CDN: 1981 BYTES: 277340 Best so far: TABLE#: 1 CST: 20762 CDN: 1 BYTES: 173
它屬于序號(hào)為2的連接順序.... 上翻到連接順序部分的開始處，可以看到:
Join order[2]: PREMIUM_PLAN_CODE [A13] CERT_INSURED_PLAN_BENEFIT_FACT [A11] CERTIFICATE [A12]
在9206中查找選定的執(zhí)行計(jì)劃的連接順序，在10g中，會(huì)更簡(jiǎn)單，我們?cè)?0053中有如下的跟蹤信息：
JOIN ORDER: 2
CST: ... CDN: ... RSC: ... RSP: ... BYTES: ...
此例中，被選擇的"JOIN ORDER"是 2.
4. 查找"好的"執(zhí)行計(jì)劃與"不好的"執(zhí)行計(jì)劃中不一樣的連接順序部分
本例中，它們第二張表的連接順序不同:
"好的"執(zhí)行計(jì)劃
Join result: cost: 58201 cdn: 1 rcz: 173
Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122
Best so far: TABLE#: 2 CST: 57706 CDN: 1981 BYTES: 277340
Best so far: TABLE#: 1 CST: 58201 CDN: 1 BYTES: 173
"不好的"執(zhí)行計(jì)劃
Join result: cost: 20762 cdn: 1 rcz: 173
Best so far: TABLE#: 0 CST: 15810 CDN: 1 BYTES: 122
Best so far: TABLE#: 2 CST: 20266 CDN: 1981 BYTES: 277340 <== this cost is different (20266 vs 57706)
Best so far: TABLE#: 1 CST: 20762 CDN: 1 BYTES: 173
5.在連接順序部分查找連接，它們是第四步中找到的產(chǎn)生成本的連接.
跟蹤文件中往上查找成本20266，你會(huì)找到如下行：
Join result: cost: 20266 cdn: 1981 rcz: 140
繼續(xù)查找是哪個(gè)連接產(chǎn)生了這個(gè)(最低)成本
Best NL cost: 20266 resp: 20266
從執(zhí)行計(jì)劃中，我們知道，它將是個(gè)NL連接，并且在這里得到了確認(rèn)。在NL連接部分繼續(xù)查找此成本。
NL Join
Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
...
OPTIMIZER PERCENT INDEX CACHING = 70
Access path: index (no sta/stp keys)
Index: PK_CIPBF_IX
TABLE: CERT_INSURED_PLAN_BENEFIT_FACT
RSC_CPU: 116668803 RSC_IO: 17885
IX_SEL: 1.0000e+00 TB_SEL: 2.1281e-04
Join: resc: 81471 resp: 20266
這里，我們看到"resp"成本的最佳成本被找到。這就是"response time"成本，例如，一個(gè)執(zhí)行計(jì)劃使用PX在盡可能短的時(shí)間內(nèi)找到答案所消耗的成本。"resc" 成本就是 "resource cost"。當(dāng)串行執(zhí)行查詢的時(shí)候，這就是資源消耗成本。完整的執(zhí)行計(jì)劃輸出中會(huì)顯示PX是否被使用。
我們需要弄清楚這個(gè)成本是怎么計(jì)算出來的。為此，我們要看看CBO是如何計(jì)算連接成本的。下面是基本計(jì)算公式，用實(shí)際值進(jìn)行替換：
Basic NL join cost formula: COST(outer) + [ CARD(outer) * COST(inner) ]
注意：在下面的公式中，"RESC(outer)"指訪問內(nèi)層表的資源消耗。"RESP(outer)"指外層表的響應(yīng)成本(使用PX)
resc = RESC(outer) + [CARD(outer) * RESC(inner)]
= 63646 + [ 1 * (rsc_cpu / cpu_factor + rsc_io) * index_cost_adj ]
= 63646 + [ 1 * ( 116668803 / 975563.49 + 17885) * 0.99 ]
= 63646 + 17824.5
= 81470.5 ~ 81471: OK
Resp = RESP(outer) + (CARD(outer) * RESC(inner) )
= 15809 + [1 * (rsc_cpu / cpu_factor + rsc_io)/(deg of join parallelism * parallel scaling factor) * index_cost_adj ]
= 15809 + [1 * (116668803 / 975563.49 + 17885)/(4 * 0.9) * 0.99 ]
= 15809 + 18004.59 / 3.6 * 0.99 = 20602.17 vs. 20266, close...but not exact...costing has fudge factors?
這種連接排列為外層表和并行內(nèi)層表的并行子表使用并行操作，但每個(gè)子表對(duì)內(nèi)層表使用完全索引訪問路徑。既然是個(gè)NL連接，很可能使用了"broadcast"PX行分布。如果沒有執(zhí)行計(jì)劃，很難知道這是否是CBO的選擇。
(deg of join parallelism * parallel scaling factor)= 4 * 0.9 = 3.6
因此，不是成本除以4，而是除以3.6
外層表的全表掃描的并行度可以在這里看到。連接的并行度設(shè)置為其中任何一張表的最高并行度。本例中，設(shè)置為表PREMIUM_PLAN_CODE的并行度。一個(gè)完整的執(zhí)行計(jì)劃輸出對(duì)于驗(yàn)證這一點(diǎn)非常有幫助。
6. 表"CERT_INSURED_PLAN_BENEFIT_FACT"的其他NL連接成本
使用FTS：
NL Join
Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809 (Note: serial tsc cost = 63646)
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
Access path: tsc Resc: 167233 (NOTE: parallel tsc cost = 41309)
Join: Resc: 230879 Resp: 57618 <== cost = 15809 + 41309 = 57118
Resc = Resc(outer) + [Card(outer) * Resc(inner) ]
= 63646 + [1 * 167233 ] (Note: Resc(Inner) is close to the value in single table access path, but not exact)
= 230879 (exact)
Resp = Resp(outer) + [Card(outer) * Resp(inner) ]
= 15809 + [ 1 * 41309 ]
= 57118 vs. 57618 (close, not exact)
使用索引快速全掃描：
NL Join
Outer table: cost: 15810 cdn: 1 rcz: 122 resp: 15809 (Note: serial tsc cost = 63646)
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
...
Access path: index (iff)
Index: PK_CIPBF_IX
TABLE: CERT_INSURED_PLAN_BENEFIT_FACT
RSC_CPU: 2865229980 RSC_IO: 52693
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
Access path: iff Resc: 55630
Join: Resc: 119276 Resp: 29717
Resc = Resc(outer) + [ Card(outer) * Resc(Inner) ]
= 63646 + [ 1 * 55630 ]
= 119276 (exact)
Resp = Resp(outer) + [Card(outer) * Resp(inner) / (degree of join parallelism) ]
= 15809 + [ 1 * 27815 / 2 ]
= 29716.5 = 29717 (exact)
其他連接類型的成本(SMJ 與 HJ)
SM Join
Outer table:
resc: 63646 cdn: 1 rcz: 122 deg: 4 resp: 15809
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
resc: 55630 cdn: 22878070 rcz: 18 deg: 2 resp: 27815
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 598 Area size: 1048576 Max Area size: 104857600 Degree: 1
Blocks to Sort: 1 Row size: 145 Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0
Total CPU sort cost: 975652
Total Temp space used: 0
SORT response Sort statistics
Sort width: 598 Area size: 20971520 Max Area size: 104857600 Degree: 4
Blocks to Sort: 1 Row size: 145 Rows: 1
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0
Total CPU sort cost: 1084058
SORT resource Sort statistics
Sort width: 598 Area size: 1048576 Max Area size: 104857600 Degree: 1
Blocks to Sort: 84029 Row size: 30 Rows: 22878070
Initial runs: 7 Merge passes: 1 IO Cost / pass: 149862
Total IO sort cost: 233891
Total CPU sort cost: 27269777197
Total Temp space used: 1288102000
SORT response Sort statistics
Sort width: 598 Area size: 20971520 Max Area size: 104857600 Degree: 2
Blocks to Sort: 42015 Row size: 30 Rows: 11439035
Initial runs: 4 Merge passes: 1 IO Cost / pass: 74932
Total IO sort cost: 129941
Total CPU sort cost: 14577786635
Merge join Cost: 381119 Resp: 188508
Resc cost = Resc(outer) + Resc(inner) + Sort_Cost(outer) + Sort_Cost(inner)
= Resc(outer) + Resc(inner) + [ (CPU_Cost(outer) + IO_Cost(outer) ) + (CPU_Cost(inner) + IO_Cost(inner) ]
= 63646 + 55630 + ( 975652 / 975563.49 + 0 ) + ( 27269777197 / 975563.49 + 233891)
= 381120.8 ~ 381119 (very close)
Resp cost = Resp(outer) + Resp(inner) + Par_Sort_Cost(outer) + Par_Sort_Cost(inner)
= Resp(outer) + Resp(inner) + [ (Par_CPU_Cost(outer) + Par_IO_Cost(outer) ) + ( (Par_CPU_Cost(inner) + Par_CPU_Cost(inner)) ]
= 15809 + 27815 + [ ( 1084058 / 975563.49 + 0 ) + ( 14577786635 / 975563.49 + 129941) ]
= 188509.1 ~ 188508 (very close)
HA Join
Outer table:
resc: 63646 cdn: 1 rcz: 122 deg: 4 resp: 15809
Inner table: CERT_INSURED_PLAN_BENEFIT_FACT
resc: 55630 cdn: 22878070 rcz: 18 deg: 2 resp: 27815
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 587 Deg: 4
hash_area: 5120 (max=25600) buildfrag: 1 probefrag: 20946 ppasses: 1
buildfrag: 1 probefrag: 20946 passes: 1
Hash join Resc: 121625 Resp: 44212
Resc cost = Resc(outer) + Resc(inner) + HJ_Cost_Ser
= 63646 + 55630 + (HJ_Resc_Cost * HJ_Dop)
= 63646 + 55630 + ( 587 * 4 )
= 121624 ~ 121625 (very close)
Resp cost = Resp(outer) + Resp(inner) + HJ_Cost_Par
= 15809 + 27815 + 587
= 44211 ~ 44212 (very close)
注意：
用于SMJ 和 HA 的RESC 與RESP成本來自于每張表的單個(gè)表訪問成本部分，例如：
RESC(inner)是IFF resc成本，RESP(inner) 是IFF resp成本。
7. 結(jié)論 本文的最終結(jié)論是，未加提示("不好的")執(zhí)行計(jì)劃，CBO選擇一個(gè)NL連接的全索引掃描。主要受以下因素影響：
1. FTS的高成本(由于"multiblock read divisor"的值過低)。
2. 由于OPTIMIZER_INDEX_CACHING參數(shù)，導(dǎo)致索引訪問的成本較低。這個(gè)參數(shù)極大的降低索引訪問的成本，對(duì)系統(tǒng)來說遠(yuǎn)遠(yuǎn)超出合理的值。
我們知道另外一個(gè)較好的執(zhí)行計(jì)劃使用全表掃描，并且具有較好的性能，可以看到，"multiblock read divisor"在本例子中設(shè)置好像不是很準(zhǔn)確。一個(gè)較準(zhǔn)確的除數(shù)，會(huì)讓FTS/IFF的成本更低，使用哈希連接會(huì)更好。另外一個(gè)更好的執(zhí)行計(jì)劃可能是對(duì)內(nèi)部行源(與哈希連接類似，但是避免了哈希成本)使用IFF嵌套循環(huán)。本例中，嵌套循環(huán)的內(nèi)部行源沒有選擇使用IFF，是因?yàn)樗某杀居?jì)算沒有考慮OPTIMIZER_INDEX_CACHING因子(只應(yīng)用于執(zhí)行單塊索引讀操作)所帶來的成本減少的益處。而且，IFF的相對(duì)成本由于較低的multiblock read divisor設(shè)置而增加了。
后續(xù)的調(diào)查包括：
找出為什么多塊讀除數(shù)如此低(在aux_stats$視圖中檢查系統(tǒng)的統(tǒng)計(jì))
一段時(shí)間間隔后，再次收集系統(tǒng)的統(tǒng)計(jì)信息，將這些信息放置于"stattab"中，比較與aux_stats$中的現(xiàn)存值。
了解客戶為什么要設(shè)置OPTIMIZER_INDEX_CACHING為一個(gè)如此高的值。
8. 參考文檔
Note: 338137.1
