oracle診斷事件及深入解析10053事件

今天在看到這篇講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 模式優化器，那麼除了這一部分外將不會有多餘資訊出現在跟蹤檔案裡。

　　優化器相關引數：

　　記載了所有影響成本計算的引數　

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

　　表

　　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 索引聚合因子　

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

　　單表訪問路徑　

　　我們看一下上面是什麼意思。首先CBO 列出了ename 列的統計資訊(第2，3 行),這些

　　統計資訊來自dba_tab_columns。

　　列的統計資訊和dba_tab_columns 中對應的列名如下　

　　第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 來測試。

　　過濾因子(FF)

　　為了理解索引訪問成本我們需要了解一下過濾因子。 過濾因子是一個介於0 和1 之

　　間的數字，反映了記錄的可選擇性。如果一個列有10 種不同的值，我們需要查詢等

　　於其中某一個值的記錄時，如果這10 種值平均分 布的話，你將得到1/10 的行數。

　　如果沒有直方圖，過濾因子為FF = 1/NDV = density

　　再來看一下過濾因子和查詢條件的關係

　　不使用繫結變數的情況:　

　　使用繫結變數的情況(8i):　

　　包含and 和or 的情況:　

　　索引訪問成本

　　現在我們知道了聚合因子的概念，我們再來看一看索引訪問的成本

　　我們來看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

　　讓我們用別的例子證明一下索引成本計算，語句為

　　索引和列的基本統計資料如下

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-667304/，如需轉載，請註明出處，否則將追究法律責任。