《一個執(zhí)行計(jì)劃異常變更引發(fā)的Oracle性能診斷優(yōu)化》要點(diǎn)：
本文介紹了一個執(zhí)行計(jì)劃異常變更引發(fā)的Oracle性能診斷優(yōu)化，希望對您有用。如果有疑問，可以聯(lián)系我們。

作者介紹

bisal,Oracle技術(shù)愛好者.利用業(yè)余時間學(xué)習(xí)并通過了SCJP1.4、Oracle 10g/11g OCP、Oracle 11g OCM認(rèn)證,國內(nèi)首批加入Oracle YEP的成員.訂閱號：bisal的個人雜貨鋪.

最近有一個OLTP應(yīng)用使用的Oracle數(shù)據(jù)庫突然出現(xiàn)性能問題,DBA發(fā)現(xiàn)有一些delete語句執(zhí)行時間驟長,消耗大量系統(tǒng)資源,導(dǎo)致應(yīng)用響應(yīng)時間變長積Q.

輔助信息：

1.應(yīng)用已經(jīng)很久未做過更新上線了.

2.據(jù)開發(fā)人員反饋,從之前的應(yīng)用日志看,未出現(xiàn)處理時間逐步變長的現(xiàn)象.

3.這是一套RAC+DG的環(huán)境,11g的版本.

4.這次突然出現(xiàn)大量執(zhí)行時間超長的SQL語句,是一條刪除語句,deletefromtablewherekey1=:1andkey2=:2and…(省略此案例不會用到的其他條件),應(yīng)用正常的處理邏輯中都會使用這條語句,因此并發(fā)較高,使用了綁定變量,key1和key2字段不是主鍵,但有索引,存在直方圖.

接下來會通過理論和實(shí)驗(yàn)相結(jié)合的方式,了解這個問題所需要涉及的一些Oracle基礎(chǔ)知識,最后再來分析這個案例.

本文目錄：

基礎(chǔ)知識介紹：

1. 可能造成SQL執(zhí)行計(jì)劃發(fā)生改變的一個示例
2. 綁定變量窺探
3. 查看綁定變量值的幾種方法
4. rolling invalidation
5. 聚簇因子(Clustering Factor)
6. 查詢執(zhí)行計(jì)劃的幾種方法
7. AWR
8. ASH
9. SQL AWR
10. 直方圖
11. SQL Profile

1、可能造成SQL執(zhí)行計(jì)劃發(fā)生改變的一個示例

什么情況下可能造成SQL執(zhí)行計(jì)劃發(fā)生改變?有很多種情況,這里拋磚引玉舉一個例子.

實(shí)驗(yàn)： 創(chuàng)建測試表t1,其中name字段設(shè)置索引,取值為10000個A和1個B.

我們看下用查詢條件name=’A’的SQL使用了什么執(zhí)行計(jì)劃.

再看下使用查詢條件name=’B’的SQL用了什么執(zhí)行計(jì)劃.

顯而易見,因?yàn)槿≈禐锳的記錄占據(jù)了10000/10001接近100%的比重,即這查詢條件返回了幾乎表的所有數(shù)據(jù),使用全表掃描的成本一般會小于使用索引的成本,由于TABLE ACCESS FULL會掃描表高水位線以下的數(shù)據(jù)塊,且為多塊讀,即一次IO會讀取多個數(shù)據(jù)塊,具體數(shù)據(jù)塊數(shù)量取決于參數(shù)db_file_multiblock_read_count,而INDEX RANGE SCAN則是單塊讀,同時若select字段不是索引字段的話,還需要回表,累積起來,IO次數(shù)就會可能很大,因此相比起來,全表掃描的IO可能會遠(yuǎn)小于索引掃描.

取值為B的記錄占據(jù)了1/10001很小的比重,因此使用索引掃描,直接訪問B*Tree二叉樹,定位到這一條數(shù)據(jù)的rowid再回表查詢所有select字段的成本要遠(yuǎn)小于掃描整張表數(shù)據(jù)的成本.

為了證明,可以查看這兩條SQL對應(yīng)的10053事件,如下是name=’A’的trace,可以看出全表掃描的成本值是49.63,索引掃描的成本值是351.26,全表掃描的成本更低一些.

如下是name=’B’的trace,可以看出全表掃描的成本值是49.40,索引掃描的成本值是2.00,索引掃描的成本值更低一些.

這個場景可以看出,Oracle的CBO模式會根據(jù)字段的取值比重調(diào)整對應(yīng)的執(zhí)行計(jì)劃,無論如何,都會選擇成本值最低的一個執(zhí)行計(jì)劃,這也是CBO優(yōu)于以前RBO的地方,這里僅用于實(shí)驗(yàn),因?yàn)橐话鉕LTP的應(yīng)用會使用綁定變量的寫法,不會像上面這種使用常量值的寫法,11g之前,可能帶來的一些負(fù)面影響就是綁定變量窺探的作用,即對于使用綁定變量窺探的SQL語句,Oracle會根據(jù)第一次執(zhí)行使用的綁定變量值來用于以后的執(zhí)行,即第一次做硬解析的時候,窺探了變量值,之后的軟解析,不再窺視,換句話說,如果上面實(shí)驗(yàn)的SQL語句使用了綁定變量,第一次執(zhí)行時name=’A’,則接下來即使使用name=’B’的SQL語句仍會使用全表掃描,不會選擇索引掃描,vice versa.相關(guān)的實(shí)驗(yàn)dbsnake的書中會有很詳細(xì)的說明,可以參考.11g之后,有了ACS自適應(yīng)游標(biāo)的新特性,會根據(jù)綁定變量值的情況可以重新生成執(zhí)行計(jì)劃,因此這種問題得到了緩解,當(dāng)然這些都是有代價的,緩解了綁定變量窺探的副作用,相應(yīng)地可能會導(dǎo)致有很多子游標(biāo),具體的算法可以參考dbsanke的書,這兒我就不班門弄斧了.11g默認(rèn)綁定變量窺探是開啟的,由以下隱藏參數(shù)控制.

綜上所述,針對這場景,如果值的選擇性顯著影響執(zhí)行計(jì)劃,則綁定變量的使用并不可靠,此時選擇字面值的方式可能會更合適一些,如果值的選擇性幾乎相同,執(zhí)行計(jì)劃不會顯著改變,此時使用綁定變量是最優(yōu)的選擇,當(dāng)然前提是OLTP系統(tǒng).

對于多次執(zhí)行SQL語句,執(zhí)行計(jì)劃發(fā)生變化的情況可能還有很多,例如11g的新特性Cardinality Feedback帶來的一些bug,包含直方圖的字段作為查詢條件但統(tǒng)計(jì)信息不準(zhǔn)等.

2、綁定變量窺探

首先什么是綁定變量?
一條SQL語句在解析階段,會根據(jù)SQL文本對應(yīng)的哈希值在庫緩存中查找是否有匹配的Parent Cursor,進(jìn)而找出是否有可重用的解析樹和執(zhí)行計(jì)劃,若沒有則要重新生成一遍,OLTP系統(tǒng)中,高并發(fā)的SQL若每次均需要重復(fù)執(zhí)行這些操作,即所謂的硬解析,消耗會比較大,進(jìn)而影響系統(tǒng)性能,所以就需要使用綁定變量.綁定變量其實(shí)就是一些占位符,用于替換SQL文本中具體輸入值,例如以下兩條SQL：

在Oracle看來,是兩條完全不同的SQL,即對應(yīng)SQL文本哈希值不同,因?yàn)閣here條件中一個id是1,一個是2,1和2的ASCII是不同的,可實(shí)際上這兩條SQL除了查詢條件不同,其他的文本字符均一致,盡管如此,這種情況下,Oracle還是會重復(fù)執(zhí)行解析的操作,生成各自的游標(biāo).

 

兩條記錄,說明Oracle認(rèn)為這兩條SQL是不同.

如果使用綁定變量：

每次將不同的參數(shù)值帶入:1中,語義和上面兩條相同,但對應(yīng)哈希值可是1個,換句話說,解析樹和執(zhí)行計(jì)劃是可以重用的.

使用綁定變量除了以上可以避免硬解析的好處之外,還有其自身的缺陷,就是這種純綁定變量的使用適合于綁定變量列值比較均勻分布的情況,如果綁定變量列值有一些非均勻分布的特殊值,就可能會造成非高效的執(zhí)行計(jì)劃被選擇.

如下是測試表：

其中name列是非唯一索引,NAME是A的有100000條記錄,NAME是B的有1條記錄,值分布是不均勻的,上一篇文章中我們使用如下兩條SQL做實(shí)驗(yàn).

其中第一條使用的是全表掃描,第二條使用了索引范圍掃描,過程和原因上篇文章中有敘述,此處就不再贅述.

如上SQL使用的是字面值或常量值作為檢索條件,接下來我們使用綁定變量的方式來執(zhí)行SQL,為了更好地說明,此處我們先關(guān)閉綁定變量窺探(默認(rèn)情況下,是開啟的狀態(tài)),他是什么我們稍后再說.

首先A為條件.

顯示使用了全表掃描.

再以B為條件.

發(fā)現(xiàn)仍舊是全表掃描,我們之前知道B值記錄只有一條,應(yīng)該使用索引范圍掃描,而且這兩個SQL執(zhí)行計(jì)劃中Rows、Bytes和Cost值完全一致.之所以是這樣,是因?yàn)檫@兒用的未開啟綁定變量窺探情況下的綁定變量,Oracle不知道綁定變量值是什么,只能采用常規(guī)的計(jì)算Cardinality方式,參考dbsnake的書,CBO用來估算Cardinality的公式如下：

收集統(tǒng)計(jì)信息后,計(jì)算如下：

約等于50001.因此無論是A還是B值,CBO認(rèn)為結(jié)果集都是50001,占據(jù)一半的表記錄總量,自然會選擇全表掃描,而不是索引掃描.

下面我們說說綁定變量窺探,是9i引入的一個新特性,其作用就是會查看SQL謂詞的值,以便生成最佳的執(zhí)行計(jì)劃,其受隱藏參數(shù)控制,默認(rèn)為開啟.

我們在綁定變量窺探開啟的情況下,再次執(zhí)行上述兩條SQL(區(qū)別僅是不用explain plan,使用dbms_xplan.display_cursor可以得到更詳細(xì)的信息),首先A為條件的SQL.

這次使用了全表掃描,窺探了綁定變量值是A.

再使用以B為條件的SQL：

仍舊采用了全表掃描,綁定變量窺探值是A,因?yàn)橹挥械谝淮斡步馕龅臅r候才會窺探綁定變量值,接下來執(zhí)行都會使用第一次窺探的綁定變量值.B的記錄數(shù)只有1條,1/100001的選擇率,顯然索引范圍掃描更合適.

為了讓SQL重新窺探綁定變量值,我們刷新共享池：

此時清空了所有之前保存在共享池中的信息,包括執(zhí)行計(jì)劃,因此再次執(zhí)行就會是硬解析,這次我們先使用B為條件.

可見窺探了綁定變量值是B,因?yàn)榭梢灾肋@個綁定變量:x的具體值,根據(jù)其值分布特點(diǎn),選擇了索引范圍掃描.

再用A為查詢條件：

此時仍舊窺探綁定變量值為B,因此還會選擇索引范圍掃描,即使A值應(yīng)該選擇全表掃描更高效.

總結(jié)：

綁定變量窺探會于第一次硬解析的時候,“窺探“綁定變量的值,進(jìn)而根據(jù)該值的信息,輔助選擇更加準(zhǔn)確的執(zhí)行計(jì)劃,就像上述示例中第一次執(zhí)行A為條件的SQL,知道A值占比重接近全表數(shù)據(jù)量,因此選擇了全表掃描.但若綁定變量列分布不均勻,則綁定變量窺探的副作用會很明顯,第二次以后的每次執(zhí)行,無論綁定變量列值是什么,都會僅使用第一次硬解析窺探的參數(shù)值,這就有可能選擇錯誤的執(zhí)行計(jì)劃,就像上面這個實(shí)驗(yàn)中說明的,第二次使用B為條件的SQL,除非再次硬解析,否則這種情況不會改變.

簡而言之,數(shù)據(jù)分布不均勻的列使用綁定變量,尤其在11g之前,受綁定變量窺探的影響,可能會造成一些特殊值作為檢索條件選擇錯誤的執(zhí)行計(jì)劃.11g的時候則推出了ACS(自適應(yīng)游標(biāo)),緩解了這個問題.

以上主要介紹了11g之前使用綁定變量和非綁定變量在解析效率方面的區(qū)別,以及綁定變量在綁定變量窺探開啟的情況下副作用的效果.雖然OLTP系統(tǒng),建議高并發(fā)的SQL使用綁定變量,避免硬解析,可不是使用綁定變量就一定都好,尤其是11g之前,要充分了解綁定變量窺探副作用的原因,根據(jù)綁定變量列值真實(shí)分布情況,才能綜合判斷綁定變量的使用正確.

3、查看綁定變量值的幾種方法

上一章我們了解了,綁定變量實(shí)際是一些占位符,可以讓僅查詢條件不同的SQL語句可以重用解析樹和執(zhí)行計(jì)劃,避免硬解析.綁定變量窺探則是第一次執(zhí)行SQL硬解析時,會窺探使用的綁定變量值,根據(jù)該值的分布特征,選擇更合適的執(zhí)行計(jì)劃,副作用就是如果綁定變量列值分布不均勻,由于只有第一次硬解析才會窺探,所以可能接下來的SQL執(zhí)行會選擇錯誤的執(zhí)行計(jì)劃.

有時可能我們需要查看某條SQL使用了什么綁定變量值,導(dǎo)致執(zhí)行計(jì)劃未用我們認(rèn)為最佳的一種.以下就介紹一些常用的查看綁定變量值的方法.

方法一：10046

使用level=4的10046事件,查看生成的trace文件.

可以看出綁定變量值是’Z’.

方法二：v$sql_bind_capture

首先找出SQL對應(yīng)的sql_id：

從v$sql_bind_capture可以看出兩個綁定變量占位符以及對應(yīng)的值.

這里有一點(diǎn)值得注意的就是,DATATYPE_STRING列的描述是“綁定變量數(shù)據(jù)類型的文本表示”,開始我認(rèn)為就是綁定變量字段的數(shù)據(jù)類型,但實(shí)際看來不是,DATATYPE_STRING列只是來告訴你綁定變量列是字符型,還是數(shù)值型.

我們此時換一下綁定變量值,發(fā)現(xiàn)v$sql_bind_capture信息未變,dbsnake的書中曾說過當(dāng)SQL執(zhí)行硬解析時綁定變量值被捕獲,并可從視圖v$sql_bind_capture中查詢.

對于執(zhí)行軟解析/軟軟解析的SQL,默認(rèn)情況下間隔15分鐘才能被捕獲,為了避免頻繁捕獲綁定變量值帶來的系統(tǒng)性能開銷,而且從常理上認(rèn)為,既然使用了綁定變量,最佳方式就是值分布均勻,只需要SQL執(zhí)行第一次硬解析時窺探一下,后續(xù)執(zhí)行的SQL執(zhí)行計(jì)劃應(yīng)該比較穩(wěn)定,因此只要能比較實(shí)時地查看第一次綁定變量值即可.間隔15分鐘受隱藏參數(shù)_cursor_bind_capture_interval控制,默認(rèn)值是900s,15分鐘.

我們嘗試將捕獲綁定變量的間隔時間調(diào)短,該參數(shù)不支持session級別修改.

執(zhí)行alter system級別操作.

等大約一分鐘,此時可以從v$sql_bind_capture查詢剛使用的綁定變量值.

方法三：AWR信息

(1)DBA_HIST_SQLBIND視圖包含了v$sql_bind_capture的快照.

因此對應(yīng)的SQL語句,和v$sql_bind_capture很像.

(2)另一個視圖,DBA_HIST_SQLSTAT記錄了SQL統(tǒng)計(jì)信息的歷史信息,他是基于一些標(biāo)準(zhǔn),捕獲來自于V$SQL的統(tǒng)計(jì)信息.可以使用如下SQL：

其中dbms_sqltune.extract_bind(bind_data,1).value_string取決于SQL中綁定變量的數(shù)量.

第一次執(zhí)行這兩條SQL時,并未有任何結(jié)果返回,我猜測可能是這條SQL不符合AWR采集的標(biāo)準(zhǔn).從MOS中查到這篇文章：《How to Control the Set of Top SQLs Captured During AWR Snapshot Generation (文檔 ID 554831.1)》,用其中的方法修改下AWR采集topnsql參數(shù).

默認(rèn)值是

含義是

此時重新執(zhí)行SQL,默認(rèn)AWR會一小時采集一次,此時可以手工采集AWR快照.

此時再次查詢DBA_HIST_SQLBIND

再次查詢DBA_HIST_SQLSTAT

綁定變量值可以使用很多方法獲取,這里只是列舉了三種最常見的方法,我從網(wǎng)上看到有朋友還有用wrh$_sqlstat、v$sql等視圖查詢的例子,沒有深究,我覺得碰見問題時,可以快速使用一些常用的方法解決問題就可以了,當(dāng)然時間充裕的話,建議還是多從原理層了解一些,做到觸類旁通則最好.

4、rolling invalidation

有一條SQL,使用了綁定變量,查看V$SQLAREA發(fā)現(xiàn)version_count是2

查看V$SQL,發(fā)現(xiàn)有兩條記錄,分別對應(yīng)了0和1兩個child cursor：

再查看這兩個child cursor對應(yīng)的執(zhí)行計(jì)劃：

child cursor：0

hild cursor：1

發(fā)現(xiàn)除了成本代價略有不同,其他訪問路徑完全一致.應(yīng)用保證使用的相同用戶執(zhí)行這條SQL語句,綁定變量窺探關(guān)閉.問題就來了,為何同一條SQL有兩個child cursor,且執(zhí)行計(jì)劃一致?

再拋一下,通過V$SQL_SHARED_CURSOR視圖可以查看游標(biāo)失效的原因,對比這兩個cursor,不同之一就是這個ROLL_INVALID_MISMATCH字段的值,0號cursor值為N,1號cursor值為Y.

另外,REASON字段,0號cursor顯示了內(nèi)容,1號cursor該字段值為空.

這個問題通過Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (文檔 ID 557661.1)這篇文章能夠很好地解釋.

這個問題通過Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (文檔 ID 557661.1)這篇文章能夠很好地解釋.

大體意思是在10g之前,使用dbms_stats采集對象統(tǒng)計(jì)信息,除非no_invalidate設(shè)為TRUE,否則所有緩存在Library Cache中的游標(biāo)都會失效,下次執(zhí)行時需要做硬解析.隱患就是對于一個OLTP系統(tǒng),會產(chǎn)生一次硬解析風(fēng)暴,消耗大量的CPU、庫緩存以及共享池latch的爭用,進(jìn)而影響應(yīng)用系統(tǒng)的響應(yīng)時間.如果設(shè)置no_invalidate為FALSE,則現(xiàn)有存儲的游標(biāo)不會使用更新的對象統(tǒng)計(jì)信息,仍使用舊有執(zhí)行計(jì)劃,直到下次硬解析,要么因?yàn)闀r間太久,導(dǎo)致cursor被刷出,要么手工執(zhí)行flush刷新了共享池,這兩種情況下會重新執(zhí)行硬解析,根據(jù)更新的對象統(tǒng)計(jì)信息,生成更新的執(zhí)行計(jì)劃.這么做其實(shí)還是有可能出現(xiàn)硬解析風(fēng)暴,特別是OLTP系統(tǒng),高并發(fā)時候,有SQL語句頻繁訪問.

使用dbms_stats.gather_XXX_stats的時候,有個參數(shù)no_invalidate：

默認(rèn)是AUTO_INVALIDATE,這表示是由Oracle來決定什么時候讓依賴的游標(biāo)失效.

10g之后,如果采集對象統(tǒng)計(jì)信息使用的no_invalidate參數(shù)是auto_invalidate,則Oracle會采用如下操作,來緩解可能的硬解析風(fēng)暴.

1. 執(zhí)行dbms_stats,所有依賴于這個已分析對象的緩存cursor游標(biāo)會被標(biāo)記為rolling invalidation,并且記錄此時刻是T0.
2. 下次某個session需要解析這個標(biāo)記為rolling invalidation的cursor游標(biāo)時,會設(shè)置一個時間戳,其取值為_optimizer_invalidation_period定義的最大值范圍內(nèi)的一個隨機(jī)數(shù).之所以是隨機(jī)數(shù),就是為了分散這些 invalidation的游標(biāo),防止出現(xiàn)硬解析風(fēng)暴.參數(shù)_optimizer_invalidation_period默認(rèn)值是18000秒,5小時.記錄這次解析時間為T1,時間戳值為Tmax.但此時,仍是重用了已有游標(biāo),不會做硬解析,不會使用更新的統(tǒng)計(jì)信息來生成一個新的執(zhí)行計(jì)劃.
3. 接下來這個游標(biāo)(標(biāo)記了rolling invalidation和時間戳)的每次使用時,都會判斷當(dāng)前時刻T2是否超過了時間戳Tmax.如果未超過,則仍使用已存在的cursor.如果Tmax已經(jīng)超過了,則會讓此游標(biāo)失效,創(chuàng)建一個新的版本(一個新的child cursor子游標(biāo)),使用更新的執(zhí)行計(jì)劃,并且新的子游標(biāo)會標(biāo)記V$SQL_SHARED_CURSOR中ROLL_INVALID_MISMATCH的值.

這些和我上面碰見的情況基本一致.

MOS是附帶了一個實(shí)驗(yàn),可以根據(jù)實(shí)驗(yàn)來體會下這種情況.
1.為了容易觀察,設(shè)置_optimizer_invalidation_period為1分鐘.

2.創(chuàng)建測試表,并采集統(tǒng)計(jì)信息.

3.執(zhí)行一次目標(biāo)SQL,并查看V$SQL_SHARED_CURSOR信息.

此時查看這條SQL的解析和執(zhí)行次數(shù)都是1.

4.再執(zhí)行一次目標(biāo)SQL,select count(*) from X;,查看這條SQL的解析和執(zhí)行次數(shù)是2.

有人曾說過,11g中未必會按照_optimizer_invalidation_period參數(shù)定義的時間產(chǎn)生新的子游標(biāo),我上面用的環(huán)境是11g,確實(shí)如此,等了2分鐘,執(zhí)行目標(biāo)SQL,仍只有一個子游標(biāo).這樣的好處有人也說了,就是更加的隨機(jī),因?yàn)槿绻麌?yán)格按照參數(shù)設(shè)置的時間失效,則有可能頻繁使用的游標(biāo)會在超時后某一時刻集中做硬解析,還是會有資源的影響,只是時間推遲了,因此如果是在超時值基礎(chǔ)上又有隨機(jī)分布,則可能會將硬解析的影響降到最低.

又等了一段時間,再查詢V$SQL.

確實(shí)產(chǎn)生了兩個子游標(biāo),這里需要注意FIRST_LOAD_TIME的時間是一樣的,因?yàn)樗莗arent父游標(biāo)的創(chuàng)建時間,顯然這兩個子游標(biāo)肯定是對應(yīng)同一個父游標(biāo),不同的就是LAST_LOAD_TIME,這是子游標(biāo)的使用時間.

再看看V$SQL_SHARED_CURSOR.

兩個子游標(biāo)信息,只有一個R項(xiàng)值有差別,R是ROLL_INVALID_MISMATCH,0號子游標(biāo)是N,1號子游標(biāo)是Y,看看官方文檔對這個字段的說明.

表示的就是標(biāo)記為rolling invalidation的游標(biāo),已經(jīng)是超過了時間窗口,此時0號子游標(biāo)已經(jīng)過期,1號子游標(biāo)使用最新的統(tǒng)計(jì)信息,來生成最新的執(zhí)行計(jì)劃.

這就解釋了為何同一條SQL,執(zhí)行計(jì)劃一致,但卻有兩個子游標(biāo)的情況.

MOS中還描述了一些游標(biāo)使用的場景：

1. 如果一個游標(biāo)被標(biāo)記為rolling invalidation,但是再不會做解析,則這個游標(biāo)不會失效,最終還是可能根據(jù)LRU被刷出共享池.
2. 如果一個游標(biāo)被標(biāo)記為rolling invalidation,后面只會解析一次,那么這個游標(biāo)依然不會失效(僅僅使用時間戳標(biāo)記),最終還是可能根據(jù)LRU被刷出共享池.
3. 頻繁使用的游標(biāo),在超過時間戳Tmax值后,下次解析時就會被置為失效.

很明顯,上面的這些方法是有效的,因?yàn)槭?biāo)記僅僅適用于這些頻繁重用的游標(biāo),對于其他場景的游標(biāo)可以忽略,未有影響.

5、聚簇因子(Clustering Factor)

聚簇因子,Clustering Factor,聽著名字就很高大上,很學(xué)術(shù).題外話,記得幾年前的一次內(nèi)部分享,dbsnake介紹一案例的時候,曾問過在場同事其中涉及的一個知識點(diǎn)是什么,如果知道就意味著你對索引的了解很深入,可惜當(dāng)時沒人反應(yīng),作為小白的我自然也不知道,當(dāng)時的這個知識點(diǎn)就是聚簇因子,下來我仔細(xì)了解了下,確實(shí)這些東東,如果經(jīng)常用到自然脫口而出,可惜這種機(jī)會只能靠自己.

我們先看下官方對CF介紹.

索引聚簇因子衡量的是索引字段存儲順序和表中數(shù)據(jù)存儲順序的符合程度.兩者存儲順序越接近,聚簇因子值就越小.

聚簇因子的用處在于可以粗略估算根據(jù)索引回表需要的IO數(shù)量.

• 如果CF值高,Oracle執(zhí)行一個相對較大的索引范圍掃描時就會需要相對多的IO數(shù)量.這些索引項(xiàng)指向的是隨機(jī)的表塊,數(shù)據(jù)庫為了根據(jù)索引檢索表中數(shù)據(jù),不得不一次又一次地讀取相同的數(shù)據(jù)塊.
• 如果CF值低,Oracle執(zhí)行一個相對較大的索引范圍掃描時就會需要相對少的IO數(shù)量.這些索引鍵值可能指向相同的數(shù)據(jù)塊,數(shù)據(jù)庫不需要重復(fù)讀取同一個數(shù)據(jù)塊.

文中還舉了一個例子,如下表EMPLOYEES中數(shù)據(jù)是按照last name的字母順序存儲的.

如果last name是索引字段,可以看出索引的存儲順序(blockXrowY可以抽象地看作rowid),即連續(xù)的幾個索引鍵值指向的是同一個數(shù)據(jù)塊.

如果此時id是索引字段,可以看出連續(xù)的幾個索引鍵值對應(yīng)的可能是不同的數(shù)據(jù)塊,而且有可能幾個順序間隔不多的鍵值指向的是同一個數(shù)據(jù)塊,如果這是一個龐大的索引和表,buffer cache再小一些,使用id字段作為檢索條件的SQL并發(fā)再高一些,很可能之前剛從數(shù)據(jù)文件中加載至buffer cache,馬上就會根據(jù)LRU算法age out,但一會又再次加載至buffer cache,反反復(fù)復(fù),各種latch等的資源爭用就會累積起來,進(jìn)而可能對系統(tǒng)性能造成影響.

DBA/ALL/USER_INDEXES視圖有一列CLUSTERING_FACTOR,表明該索引的聚簇因子值.

摘自dbsnake書中對于CF值計(jì)算算法的敘述：

1.CF初始值是1.

2.Oracle首先定為至目標(biāo)索引最左邊的葉子塊.

3.從最左邊的葉子塊的第一個索引鍵值所在的索引行開始順序掃描,Oracle比較當(dāng)前索引行的roid和他之前相鄰的索引行的rowid,若這兩rowid并不是指向同一個表塊,則將聚簇因子值遞增1,如果指向同一個rowid,則不改變當(dāng)前聚簇因子值.比對rowid的時候并不需要回表訪問相應(yīng)的表塊.(注：原因就是根據(jù)rowid的值是可以計(jì)算出block信息)

直到順序掃描完目標(biāo)索引所有葉子塊的所有索引行.

4.掃描操作完成后,聚簇因子當(dāng)前值就是會被存儲在數(shù)據(jù)字典中,就是上面視圖中CLUSTERINGFACTOR列.

5.說了這么多,CF有什么實(shí)際意義?個人理解,CBO模式的優(yōu)化器會綜合考慮各種因素來判斷一條SQL不同執(zhí)行計(jì)劃對應(yīng)的成本值,選擇成本值最低的一個執(zhí)行計(jì)劃,CF實(shí)際影響的是根據(jù)索引回表需要的IO數(shù)量,自然也在其考慮的范圍之內(nèi),因此CF值的高低有時會影響CBO對不同執(zhí)行計(jì)劃的選擇.

實(shí)驗(yàn)：

1.創(chuàng)建測試表

測試表有兩列NUMBER類型的字段,其中id1是按照順序存儲,id2是無序存儲,id1和id2各有一個非唯一索引.

2.采集統(tǒng)計(jì)信息

DBA/ALL/USER_INDEXES中有一注釋：

Column names followed by an asterisk are populated only if you collect statistics on the index using the DBMS_STATS package.“

即使用DBMS_STATS包收集索引統(tǒng)計(jì)信息的時候,CLUSTERING_FACTOR才會有值.

從dba_indexes中可以看出id1對應(yīng)的索引CF只有204,id2對應(yīng)的索引CF有99481,表的數(shù)據(jù)量是100000,就是說這個id2中所有葉子塊的索引行排列順序幾乎和表中數(shù)據(jù)存儲的順序完全不一致.

3.CF對執(zhí)行計(jì)劃選擇的影響

使用id1 between 1 and 1000作為檢索條件,可以看出使用了id1索引范圍掃描.

使用id2 between 1 and 1000作為檢索條件,這次卻選擇了全表掃描,沒有選擇id2索引掃描.

如果我們強(qiáng)制使用id2索引,無論從Cost,還是consistent gets,都要高于全表掃描.

究其原因,還可以參考dbsnake書中對于索引范圍掃描的算法.

我們可以檢索視圖發(fā)現(xiàn),id1和id2的索引LEAF_BLOCKS等列值均相等,只有CLUSTERING_FACTOR不同,進(jìn)而可以粗略認(rèn)為索引范圍掃描的成本和聚簇因子的大小成正比.

進(jìn)而我們可以這么嘗試,人為將id2的索引聚簇因子值改為200.

可以看出此時選擇了id2的索引范圍掃描.

但相應(yīng)consistent gets值依舊很大,我猜原因就是計(jì)算執(zhí)行計(jì)劃成本值,CBO會根據(jù)相關(guān)統(tǒng)計(jì)信息值來計(jì)算,我們?nèi)藶樵O(shè)置了索引的聚簇因子為一個很小的值,計(jì)算出來的成本值小于全表掃描,因此選擇了使用索引的執(zhí)行計(jì)劃,但實(shí)際回表等操作需要消耗的資源其實(shí)并沒有少.

如果要消除聚簇因子的影響,只能對表中數(shù)據(jù)按照目標(biāo)索引鍵值的順序重新存儲,例如,create table t1_cf_0 as select * from t1_cf order by id2;

但這么做帶來的問題就是,可能id2的聚簇因子下降了,相對id1的聚簇因子上升了,有些顧此失彼的意思.因此根據(jù)實(shí)際業(yè)務(wù)需求,選擇正確的表數(shù)據(jù)組織形式,或者只能通過其他優(yōu)化方式,來減小聚簇因子的影響.

之前曾發(fā)過一個如何讓CF值小的討論帖,有興趣的朋友可以參考,
http://www.itpub.net/thread-1910003-1-1.html

總結(jié)：?

• 聚簇因子表示索引鍵值的排列順序和表中數(shù)據(jù)排列順序的相似程度.
• 可以粗略認(rèn)為索引范圍掃描的成本,和聚簇因子的大小成正比,從索引范圍掃描的計(jì)算方法可以推出這個結(jié)論.
• 是否需要重新組織表中數(shù)據(jù)存儲順序,以降低某一個索引的聚簇因子值,需要結(jié)合實(shí)際需求來判斷,因?yàn)槿舯碇写嬖诙鄠€索引,很可能造成顧此失彼的情況.

文章來自微信公眾號：DBAplus社群

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/4135.html