作者簡介：

劉晨，網(wǎng)名bisal,，Oracle 10g/11g OCM,，并國內(nèi)首批Oracle YEP成員，

博客：blog.itpub.net/bisal

今天快下班的時(shí)候,，幾位兄弟來聊一個(gè)問題,，大致是昨天應(yīng)用使用的數(shù)據(jù)庫突然出現(xiàn)性能問題，DBA發(fā)現(xiàn)有一些delete語句執(zhí)行時(shí)間驟長,，消耗大量系統(tǒng)資源,，導(dǎo)致應(yīng)用響應(yīng)時(shí)間變長積Q。

目前掌握的信息如下： 

(1) 應(yīng)用已經(jīng)很久未做過更新上線了,。
(2) 據(jù)開發(fā)人員反饋,，從之前的應(yīng)用日志看，未出現(xiàn)處理時(shí)間逐步變長的現(xiàn)象。
(3) 這是一套RAC+DG的環(huán)境,，版本未知,，猜測至少應(yīng)該是11g的版本。
(4) 這次突然出現(xiàn)大量執(zhí)行時(shí)間超長的SQL語句,，是一系列delete語句,，例如delete from table where key=:1or key=:2 … key=:13這種SQL，應(yīng)用正常的處理邏輯中都會使用這條語句,，因此并發(fā)較高,，使用了綁定變量，key字段不是主鍵,，但有索引,。目前尚不知曉字段是否存在直方圖。
(5) 表的數(shù)據(jù)量大約5000萬,，初步反饋得知key=0的記錄大約1500萬,，執(zhí)行時(shí)間超長的SQL語句都使用了key=0的條件，至于key=0的真實(shí)數(shù)據(jù)量,，以及出現(xiàn)問題的SQL語句使用的綁定變量具體值,，這些還需要開發(fā)再次確認(rèn)。
(6) DBA反饋SQL語句執(zhí)行計(jì)劃發(fā)生了變化,，從數(shù)據(jù)庫層面做了一些操作后,，問題解決，目前尚不知曉做了什么具體的操作,。

已知的上述信息很有限,，不能給出非常明確的出錯(cuò)原因，需要明日進(jìn)一步和開發(fā)以及DBA了解后才能做深入的分析,，了解真正的問題根源。

什么情況下可能造成SQL執(zhí)行計(jì)劃發(fā)生改變,？

有很多種情況,，這里拋磚引玉舉一個(gè)例子。再次聲明,，以下實(shí)驗(yàn)和上面的問題可能沒有直接關(guān)系,，僅是引申的一些觀點(diǎn)，上面問題的根源還有待進(jìn)一步確認(rèn)和排查,。

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

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

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

顯而易見，因?yàn)槿≈禐锳的記錄占據(jù)了10000/10001接近100%的比重，即這查詢條件返回了幾乎表的所有數(shù)據(jù),。使用全表掃描的成本一般會小于使用索引的成本,，由于TABLE ACCESS FULL會掃描表高水位線以下的數(shù)據(jù)塊，且為多塊讀,，具體數(shù)據(jù)塊數(shù)量取決于參數(shù)db_file_multiblock_read_count,，而INDEX RANGESCAN則是單塊讀，同時(shí)若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,，索引掃描的成本值更低一些,。

這個(gè)場景可以看出，Oracle的CBO模式會根據(jù)字段的取值比重調(diào)整對應(yīng)的執(zhí)行計(jì)劃,，無論如何,，都會選擇成本值最低的一個(gè)執(zhí)行計(jì)劃，這也是CBO優(yōu)于以前RBO的地方,，這里僅用于實(shí)驗(yàn),，因?yàn)橐话鉕LTP的應(yīng)用會使用綁定變量的寫法，不會像上面這種使用常量值的寫法,。11g之前,，可能帶來的一些負(fù)面影響就是綁定變量窺探的作用，即對于使用綁定變量窺探的SQL語句,，Oracle會根據(jù)第一次執(zhí)行使用的綁定變量值來用于以后的執(zhí)行,，即第一次做硬解析的時(shí)候,，窺探了變量值，之后的軟解析,，不再窺視,。

如果上面實(shí)驗(yàn)的SQL語句使用了綁定變量，第一次執(zhí)行時(shí)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)然這些都是有代價(jià)的,，緩解了綁定變量窺探的副作用，相應(yīng)地可能會導(dǎo)致有很多子游標(biāo),，具體的算法可以參考dbsanke的書,，這兒我就不班門弄斧了。11g默認(rèn)綁定變量窺探是開啟的,，由以下隱藏參數(shù)控制,，

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

對于多次執(zhí)行SQL語句，執(zhí)行計(jì)劃發(fā)生變化的情況可能還有很多,，例如11g的新特性Cardinality Feedback帶來的一些bug，包含直方圖的字段作為查詢條件但統(tǒng)計(jì)信息不準(zhǔn)(dbsnake的書中有一個(gè)案例)等,，有機(jī)會做一些實(shí)驗(yàn),，再呈現(xiàn)出來。

接下來我將介紹跟案例相關(guān)的一些基礎(chǔ)知識,。

首先什么是綁定變量,？

一條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：

select * from t1 where id = 1;

select * fromt1 where id = 2;

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

兩條記錄,，說明Oracle認(rèn)為這兩條SQL是不同。如果使用綁定變量,，

select * from t1 where id = :1;

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

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

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

select* from t1 where name = 'A';

select* from t1 where name = 'B';

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

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

首先A為條件

顯示使用了全表掃描,。再以B為條件，

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

ComputedCardinality = Original Cardinality * Selectivity 
Selectivity = 1 / NUM_DISTINCT

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

Computed Cardinality = 100001 * 1 / 2

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

下面我們說說綁定變量窺探，是9i引入的一個(gè)新特性,，其作用就是會查看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(shí)候才會窺探綁定變量值,，接下來執(zhí)行都會使用第一次窺探的綁定變量值。B的記錄數(shù)只有1條,，1/100001的選擇率,，顯然索引范圍掃描更合適。為了讓SQL重新窺探綁定變量值,，我們刷新共享池,，

altersystem flush shared_pool;

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

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

再用A為查詢條件,， 

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

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

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

雖然OLTP系統(tǒng)，建議高并發(fā)的SQL使用綁定變量,，避免硬解析,，可不是使用綁定變量就一定都好，尤其是11g之前,，要充分了解綁定變量窺探副作用的原因,，根據(jù)綁定變量列值真實(shí)分布情況，才能綜合判斷綁定變量的使用正確,。

本文首先描述了與SQL執(zhí)行計(jì)劃相關(guān)的案例,，然后圍繞案例介紹了相關(guān)的基礎(chǔ)知識，11g之前使用綁定變量和非綁定變量在解析效率方面的區(qū)別,，以及綁定變量在綁定變量窺探開啟的情況下副作用的效果,。

那么該SQL執(zhí)行計(jì)劃相關(guān)的案例如何處理，我們下期再展開討論,。