Oracle优化12-10053事件
思維導圖
10053事件概述
我們在查看一條SQL語句的執行計劃時，只看到了CBO最終告訴我們的執行計劃結果，但是我們并不知道CBO為何要這樣做。
特別是當執行計劃明顯失真時，我們特別想搞清楚為什么CBO會做出這樣的一個選擇，那么就可以用10053事件來分析SQL分析過程的trace文件。
同10046事件一樣，10053事件依然無法在官網上找到相關的信息。
10053事件為我們真正的揭開蒙在CBO身上的面紗。
如何做10053事件分析
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as zmc@xgjSQL> create table t as select rownum x from dba_objects;Table createdSQL> create index index_t on t(x);Index created##同時對表和索引進行分析 SQL> exec dbms_stats.gather_table_stats(user,'t',cascade => true);PL/SQL procedure successfully completedSQL> create table t1 as select x , 'T1' name from t where x < 10000;Table created##開啟10053事件分析 SQL> alter session set events '10053 trace name context forever,level 1';Session altered##執行計劃 SQL> explain plan for select t1.* from t ,t1 where t.x<100 and t.x=t1.x;Explained##關閉10053事件 SQL> alter session set events '10053 trace name context off';Session altered我們可以發現，10053事件的使用方法和10046是一樣的，首先需要個事件設置一個級別level 1 ,然后運行SQL（或者直接使用explain plan的方式產生執行計劃），最后終止事件。
獲取10053生成的trace文件
10053事件同10046事件一樣也會在同樣的路徑下產生一個trace文件。 值得注意的是，10053生成的trace文件不能夠使用tkprof工具處理。 tkprof工具只能處理sql_trace或者100046事件產生的trace文件。 對于10053事件我們直接閱讀原始文件即可。
如何獲取，查看10046事件獲取trace文件的方法步驟，不再贅述了
oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *4348* cc_ora_4348.trc cc_ora_4348.trm分析10053生成的trace文件
查看cc_ora_4348.trc原文件，第一部分都是trace文件通用的，包含操作系統，數據庫和會話的信息等等…
10053事件從 Predicate Move-Around (PM) 開始 進入 10053事件的trace信息部分， 這一部分CBO的主要工作是對SQL語句的謂詞進行分析、重寫，把它改寫成最符合邏輯的SQL語句.
接下來的部分：
這一部分解釋trace文件中常用到的一些縮寫的指標含義，在trace的開頭列舉出來，以便更加容易的閱讀trace文件。
從邏輯上看 “T”.”X”<100 AND “T”.”X”=”T1”.”X” 和 “T”.”X”<100 AND “T”.”X”=”T1”.”X” AND “T1”.”X”<100 這兩個謂詞條件上是等價的，CBO把它改寫成這樣，主要是為了方便統計每一步的成本和估算Cardinality(基數)。
下面是綁定變量的描述
Peeked values of the binds in SQL statement
如果SQL中有變量綁定，并且SQL語句執行了bind peeking,在這一項中會有相應的信息。
接下來就是
BASE STATISTICAL INFORMATION， 主要是SQL語句中飲用到的基本對象信息，包括關聯表和各自索引的信息，這些信息可以在相關的視圖中查到比如user_tables 和 user_index,這些值在CBO計算代價的時候都會被考慮到。
可以看到這部分的信息一共列出了3個對象信息： t表 t1表 INDEX_T。
表信息的部分中包含了表的行數，數據塊數，平均行長， 對于字段，只列出了謂詞條件中包含的字段，對謂詞中沒有出現的字段，因為它不影響執行計劃的選擇，所以CBO不考慮將它考慮到代價中來。
我們看到，這里列出的字段是x字段。因為它既是兩表關聯的字段，同時自身也是一個謂詞條件。
字段部分：X列的信息包含了它的類型、平均長度、非重復的值、空值、密度及列的最大最小值，這些信息在CBO做執行計劃的計算上都要作為輸入的值。
索引部分中列出了 索引的高度、索引頁數塊（LB, Leaf Blocks）,每個索引鍵值占據的數據塊數（LB/K, Leaf Blocks/Key）,每個索引鍵值對應的數據塊數（DB/K,Data Blokcs/Key）, 索引的聚合因子（CLUF，Clustering Factor）.
值得一提的是 CLUF索引的聚合因子。 它表示索引中的鍵值和原表上的數據分部的一種關系，當索引鍵值和表中數據的排列順序大致相同時，它以為著索引鍵值指向的數據塊越集中，CLUF 因子越小，越有利于索引的使用。 反之，CLUF的值越大，越不利于索引的使用。
了解這個指標對我們分析SQL執行計劃很有用處，比如當我們發現SQL執行計劃異常，可是從Cardinality上無法解釋時，也許應該考慮下是否是Clustering Factor的影響導致的。
接下來的部分是CBO計算的每個對象單獨訪問的代價。 CBO要計算出每個對象單獨訪問時的代價，通過比較所有的數據訪問代價，選擇出代價最小的一種訪問方式：
SINGLE TABLE ACCESS PATH
T表：
這里面有2個指標對對于我們分析執行計劃比較重要
Card: Original: 35252.000000
原始記錄數,也就是操作數據源的輸入記錄數，在這里就是表的實際記錄數35252
SQL> select count(1) from t ; COUNT(1) ---------- 35252Rounded: 99
輸出的記錄數，CBO計算出通過這些過濾條件，預計得到的記錄數?？芍蠗l件的記錄99條，CBO估算出99條。（有些時候可能不是一樣，有可能是比較接近實際值）
SQL> select count(1) from t ,t1 where t.x<100 and t.x=t1.x; COUNT(1) ---------- 99T1表：
T1忘記做表分析了…
T1表，忘記建索引了，只能全表掃描….
至此，CBO 計算出了每個表單獨訪問數據代價的最小方式，為下一步多表關聯查詢提供代價計算的數據依據。
下面的部分CBO會列出 T、 T1表所有的關聯方式，并計算出每一種關聯方式的代價，最終選擇出代價最小的關聯方式作為SQL的執行計劃：
這里面會有六種情況：
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[1]: T1[T1]#0 T[T]#1 （T1關聯 T）
Considering cardinality-based initial join order. Permutations for Starting Table :0 Join order[1]: T1[T1]#0 T[T]#1*************** Now joining: T[T]#1 *************** NL JoinOuter table: Card: 99.00 Cost: 7.08 Resp: 7.08 Degree: 1 Bytes: 17 Access path analysis for TInner table: T Alias: TAccess Path: TableScanNL Join: Cost: 1643.96 Resp: 1643.96 Degree: 1Cost_io: 1618.00 Cost_cpu: 742442284Resp_io: 1618.00 Resp_cpu: 742442284Access Path: index (index (FFS))Index: INDEX_Tresc_io: 21.14 resc_cpu: 6548312ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Inner table: T Alias: TAccess Path: index (FFS)NL Join: Cost: 2122.74 Resp: 2122.74 Degree: 1Cost_io: 2100.00 Cost_cpu: 650434250Resp_io: 2100.00 Resp_cpu: 650434250 kkofmx: index filter:"T"."X"<100Access Path: index (AllEqJoinGuess)Index: INDEX_Tresc_io: 1.00 resc_cpu: 8171ix_sel: 0.000028 ix_sel_with_filters: 0.000000 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 50.00***** End Logdef Adjustment ****** NL Join : Cost: 106.10 Resp: 106.10 Degree: 1Cost_io: 106.00 Cost_cpu: 2965253Resp_io: 106.00 Resp_cpu: 2965253Best NL cost: 106.10resc: 106.10 resc_io: 106.00 resc_cpu: 2965253resp: 106.10 resp_io: 106.00 resc_cpu: 2965253 Join Card: 98.012780 = outer (99.000000) * inner (99.002808) * sel (0.010000) Join Card - Rounded: 98 Computed: 98.01Outer table: T1 Alias: T1resc: 7.08 card 99.00 bytes: 17 deg: 1 resp: 7.08Inner table: T Alias: Tresc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00using dmeth: 2 #groups: 1SORT ressource Sort statisticsSort width: 1227 Area size: 1048576 Max Area size: 214743040Degree: 1Blocks to Sort: 1 Row size: 29 Total Rows: 99Initial runs: 1 Merge passes: 0 IO Cost / pass: 0Total IO sort cost: 0 Total CPU sort cost: 28632647Total Temp space used: 0SORT ressource Sort statisticsSort width: 1227 Area size: 1048576 Max Area size: 214743040Degree: 1Blocks to Sort: 1 Row size: 16 Total Rows: 99Initial runs: 1 Merge passes: 0 IO Cost / pass: 0Total IO sort cost: 0 Total CPU sort cost: 28632647Total Temp space used: 0SM join: Resc: 11.08 Resp: 11.08 [multiMatchCost=0.00] SM JoinSM cost: 11.08 resc: 11.08 resc_io: 9.00 resc_cpu: 59450866resp: 11.08 resp_io: 9.00 resp_cpu: 59450866Outer table: T1 Alias: T1resc: 7.08 card 99.00 bytes: 17 deg: 1 resp: 7.08Inner table: T Alias: Tresc: 2.00 card: 99.00 bytes: 5 deg: 1 resp: 2.00using dmeth: 2 #groups: 1Cost per ptn: 0.02 #ptns: 1hash_area: 256 (max=52428) buildfrag: 1 probefrag: 1 ppasses: 1Hash join: Resc: 9.10 Resp: 9.10 [multiMatchCost=0.00] HA JoinHA cost: 9.10 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323resp: 9.10 resp_io: 9.00 resp_cpu: 2810323 Best:: JoinMethod: HashCost: 9.10 Degree: 1 Resp: 9.10 Card: 98.01 Bytes: 22 *********************** Best so far: Table#: 0 cost: 7.0752 card: 99.0000 bytes: 1683Table#: 1 cost: 9.0983 card: 98.0128 bytes: 2156 ***********************Join order[2]: T[T]#1 T1[T1]#0（T關聯 T1）
Join order[2]: T[T]#1 T1[T1]#0*************** Now joining: T1[T1]#0 *************** NL JoinOuter table: Card: 99.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 5 Access path analysis for T1Inner table: T1 Alias: T1Access Path: TableScanNL Join: Cost: 574.45 Resp: 574.45 Degree: 1Cost_io: 567.00 Cost_cpu: 213015937Resp_io: 567.00 Resp_cpu: 213015937Best NL cost: 574.45resc: 574.45 resc_io: 567.00 resc_cpu: 213015937resp: 574.45 resp_io: 567.00 resc_cpu: 213015937 Join Card: 98.012780 = outer (99.002808) * inner (99.000000) * sel (0.010000) Join Card - Rounded: 98 Computed: 98.01Outer table: T Alias: Tresc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00Inner table: T1 Alias: T1resc: 7.08 card: 99.00 bytes: 17 deg: 1 resp: 7.08using dmeth: 2 #groups: 1SORT ressource Sort statisticsSort width: 1227 Area size: 1048576 Max Area size: 214743040Degree: 1Blocks to Sort: 1 Row size: 29 Total Rows: 99Initial runs: 1 Merge passes: 0 IO Cost / pass: 0Total IO sort cost: 0 Total CPU sort cost: 28632647Total Temp space used: 0SM join: Resc: 10.08 Resp: 10.08 [multiMatchCost=0.00] SM JoinSM cost: 10.08 resc: 10.08 resc_io: 9.00 resc_cpu: 30818220resp: 10.08 resp_io: 9.00 resp_cpu: 30818220Outer table: T Alias: Tresc: 2.00 card 99.00 bytes: 5 deg: 1 resp: 2.00Inner table: T1 Alias: T1resc: 7.08 card: 99.00 bytes: 17 deg: 1 resp: 7.08using dmeth: 2 #groups: 1Cost per ptn: 0.02 #ptns: 1hash_area: 256 (max=52428) buildfrag: 1 probefrag: 1 ppasses: 1Hash join: Resc: 9.10 Resp: 9.10 [multiMatchCost=0.00] HA JoinHA cost: 9.10 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323resp: 9.10 resp_io: 9.00 resp_cpu: 2810323 Join order aborted: cost > best plan cost ***********************名詞解釋：
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)從trace文件開頭部分可以查看
Join order[1]: T1[T1]#0 T[T]#1
T1關聯 T 可以提取到的信息：
NL Join
Access Path: TableScanNL Join: Cost: 1643.96 Resp: 1643.96 Degree: 1Cost_io: 1618.00 Cost_cpu: 742442284Resp_io: 1618.00 Resp_cpu: 742442284Access Path: index (FFS)NL Join: Cost: 2122.74 Resp: 2122.74 Degree: 1Cost_io: 2100.00 Cost_cpu: 650434250Resp_io: 2100.00 Resp_cpu: 650434250Access Path: index (AllEqJoinGuess)Index: INDEX_TNL Join : Cost: 106.10 Resp: 106.10 Degree: 1Cost_io: 106.00 Cost_cpu: 2965253Resp_io: 106.00 Resp_cpu: 2965253結論：（NL Join 的最小Cost是1-6.10 ） Best NL(Nested loop) NL Join : Cost: 106.10 Resp: 106.10 Degree: 1SM Join
SM cost: 11.08 resc: 11.08 resc_io: 9.00 resc_cpu: 59450866resp: 11.08 resp_io: 9.00 resp_cpu: 59450866HA Join
HA cost: 9.10 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323resp: 9.10 resp_io: 9.00 resp_cpu: 2810323NL Join SM Join HAJoin 再對比得出最小cost
Best JoinMethod: Hash
Cost: 9.10 Degree: 1 Resp: 9.10 Card: 98.01 Bytes: 22
Best so far: Table#: 0 cost: 7.0752 card: 99.0000 bytes: 1683
Table#: 1 cost: 9.0983 card: 98.0128 bytes: 2156
Join order[2]: T[T]#1 T1[T1]#0
T關聯 T1 可以提取到的信息：
(T1沒有建索引)
NL Join
SM Join
SM cost: 10.08 resc: 10.08 resc_io: 9.00 resc_cpu: 30818220resp: 10.08 resp_io: 9.00 resp_cpu: 30818220HA Join
HA cost: 9.10 resc: 9.10 resc_io: 9.00 resc_cpu: 2810323resp: 9.10 resp_io: 9.00 resp_cpu: 2810323NL Join SM Join HAJoin 再對比得出最小cost
可知道，Join order[2] 最小cost也是Ha
對比下 Join order[1] 和 Join order[2] 的Ha Cost
感覺應該是 1 和 2 中最小的cost 對比， 這里都是HA最小
看trace里1 和 2 的HA 是一樣的…..
然而Oracle確放棄了這個（不知道跟T1沒有索引有沒有關系） ，是個疑問
總之Oracle的選擇是：
Join order aborted: cost > best plan cost
Number of join permutations tried: 2
oralce通過對比之后實際選擇的關聯順序
CBO先估量出 T1 和 T 使用什么方式 掃描 最優，從上面對表的分析我們也可以看到 對T1 才去的是全表掃描，對T采取的是索引 。
然后再估量出 這兩個表使用何種關聯方式最優，最終得到一個執行計劃。
經過一些列的比較，Oracle最終選擇了如上的執行計劃作為SQL的最終執行計劃。
接下來trace文件最后一部分依然是參數和bug修復信息，忽略…
總結
通過分析10053事件的trace原文件，我們會發現，CBO一定最終選擇的是代價最低的數據訪問路徑作為SQL的執行計劃。
如果我們覺得CBO做出的執行計劃不是最優的，就應該去分析，比如對于CBO選擇的每一個代價最低的訪問數據方式，我們提供給CBO的分析信息是否真實？ 抑或是代價高的數據訪問方式的分析是否真實？
因為CBO只不過是一個數據模型，它只是機械的將搜集到的各種信息通過固定的方式進行計算，如果我們能夠保證給CBO提供的各種信息是正確的，CBO通常就應該會計算出最優的執行計劃。
10053事件給我們提供了一種深入CBO的內部去查看CBO如何工作的方式，不僅能看到ORACLE是根據什么樣的一句來得出最終的執行計劃，同時我們也能人為的去驗證CBO使用的一些統計數據的準確性。
總結
以上是生活随笔為你收集整理的Oracle优化12-10053事件的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Shell脚本攻略05-数组和关联数组
- 下一篇： Oracle优化11-10046事件