有朋友和我討論根因分析的問題，他認(rèn)為大多數(shù)情況下根因分析是空中樓閣，因?yàn)閿?shù)據(jù)庫的問題的根因都在數(shù)據(jù)庫產(chǎn)品的原代碼上。這個(gè)觀點(diǎn)有一定的道理，他所說的根因是絕對(duì)的根因，并不是日常為解決問題而去尋找的根因。這個(gè)世界上我們無法窮盡自然規(guī)律去找到最終的根因，但是我們能夠找到那個(gè)與解決問題有關(guān)的關(guān)鍵問題，我們暫且稱之為“根因”。不過當(dāng)我們對(duì)某些原理理解不夠深入和全面的時(shí)候，我們就無法找到那個(gè)解決問題的關(guān)鍵，這就是我一直堅(jiān)持的那個(gè)理解數(shù)據(jù)庫的本質(zhì)的主要原因。我寫《DBA的思想天空》也是希望DBA在成長過程中多去掌握一些數(shù)據(jù)庫原理性的技術(shù)，而不是僅僅學(xué)習(xí)一些技巧。

有時(shí)候我們發(fā)現(xiàn)數(shù)據(jù)庫中有幾條SQL總是有點(diǎn)問題，而且問題出在CURSOR共享上，但是我們經(jīng)常找不出根因來。實(shí)際上共享池問題的復(fù)雜性超出了一些DBA的認(rèn)知極限，里面涉及的技術(shù)原理實(shí)在是太復(fù)雜了。

Oracle的CURSOR SHARING技術(shù)的關(guān)鍵部分是LIBRARY CACHE的管理。LIBRARY CACHE也是按照HASH CHAINS的方式來管理的，會(huì)被分為多個(gè)HASH BUCKET。當(dāng)要定位某條SQL的時(shí)候是通過HASH算法來快速定位的。    

圖片

首先根據(jù)SQL語句生成HASH值，然后通過HASH CHAINS找到相關(guān)的的Object Handle。對(duì)于Hash Chains，大家都不陌生，數(shù)據(jù)庫中的很多鏈表管理，都是采用HASH桶的方式管理的。對(duì)于HASH桶，大家立即就會(huì)想到均衡問題，對(duì)于CACHE BUFFER CHAINS，不均衡的HASH桶會(huì)產(chǎn)生訪問性能不佳的HOT BUFFER，而對(duì)于LIBRARY CACHE HASH BUCKETS，則會(huì)產(chǎn)生訪問延時(shí)過大的library cache handle。過于長的HASH CHAINS在數(shù)據(jù)庫中的表現(xiàn)可能會(huì)導(dǎo)致library cache閂鎖的等待出現(xiàn)異常。    

圖片

如果某個(gè)bucket上的鏈表比較長，那么如果OBJECT HANDLE在這條鏈上，相關(guān)的SQL的執(zhí)行效率就會(huì)受到一定的影響。因此一般情況下，HASH CHAIN的深度都不會(huì)太大。我們可以通過下面的命令來DUMP一下LIBRARY CACHE。用SYSDBA登錄數(shù)據(jù)庫，然后執(zhí)行：    

oradebug setmypid;

oradebug dump library_cache 2;

圖片

可以看到LIBRARY CACHE TABLE一共有131072個(gè)BUCKET，一共有52101個(gè)對(duì)象。Oracle的 library cache bucket機(jī)制是十分古老的機(jī)制，我第一次了解到這個(gè)概念是在Oracle 7上遇到的一個(gè)BUG。這個(gè)BUG讓我認(rèn)識(shí)了_KGL_BUCKET_COUNT這個(gè)隱含參數(shù)。Oracle的LIBRARY CACHE HASH BUCKET的最小數(shù)量是509。不過這個(gè)值相當(dāng)小，如果一個(gè)數(shù)據(jù)庫實(shí)例中只有這么多個(gè)BUCKET，那么一個(gè)桶上將會(huì)鏈接過多的HANDLE了。從上面DUMP的例子可以看到，13萬+的桶上有5萬多個(gè)HANDLE。

Oracle的算法是，當(dāng)桶上的平均深度超過2的時(shí)候，BUCKET會(huì)自動(dòng)擴(kuò)容一倍。當(dāng)數(shù)據(jù)庫實(shí)例啟動(dòng)后，最多自動(dòng)擴(kuò)容7次，也就是會(huì)擴(kuò)大到128倍。似乎這個(gè)算法也沒啥毛病。不過當(dāng)HASH BUCKET擴(kuò)容的時(shí)候，所有的HASH鏈都要重組，這是一個(gè)十分高開銷的操作。二十多年前我就在一套ORACLE 7上遇到過這樣的問題。當(dāng)時(shí)我通過開SR知道了一個(gè)參數(shù):_kgl_bucket_count。這個(gè)隱含參數(shù)確定了當(dāng)數(shù)據(jù)庫實(shí)例啟動(dòng)的時(shí)候，HASH BUCKET的數(shù)量。1代表509，2代表1021，3代表2041，都是對(duì)應(yīng)2的冪次略小的素?cái)?shù)。以此類推，9是最大值，代表131071，可以管理131072個(gè)桶。這正好和上面DUMP的例子相吻合。    

圖片

下面我們先來刷新一下共享池，再來做一個(gè)DUMP，看看會(huì)有什么結(jié)果出現(xiàn)。

圖片

可以看出，存在較多對(duì)象的Buckets消失了。這個(gè)時(shí)候去訪問這些library cache objects，性能就不會(huì)有問題了。在大多數(shù)情況下，刷新共享池是可以解決此類library cache問題的。當(dāng)然，刷新共享池對(duì)于一些關(guān)鍵系統(tǒng)來說依然是有風(fēng)險(xiǎn)的。因?yàn)榇罅康腟QL會(huì)重新解析，共享池爭(zhēng)用會(huì)在瞬間加大，因此此類操作不能在業(yè)務(wù)高峰期執(zhí)行。另外一個(gè)風(fēng)險(xiǎn)是重新編譯SQL可能會(huì)因?yàn)槟承?shù)據(jù)庫表和索引的統(tǒng)計(jì)信息不準(zhǔn)確而導(dǎo)致新的SQL執(zhí)行計(jì)劃變壞，引發(fā)其他的性能問題。    

今天我們可以學(xué)到的一個(gè)技巧就是，遇到一些無法解釋根因的SQL解析性能問題，如果只是集中在某個(gè)或者某幾個(gè)SQLID上，那么可以通過對(duì)library cache做一個(gè) level 2的DUMP就可以分析今天所說的這個(gè)原因了。也許我們以后做數(shù)據(jù)庫巡檢的時(shí)候，也可以在非業(yè)務(wù)高峰期做一下DUMP，分析一下共享池是否存在這種風(fēng)險(xiǎn)。

說到今天的這個(gè)話題，想起了20多年前的一個(gè)案例，當(dāng)時(shí)的數(shù)據(jù)庫是7.3，當(dāng)時(shí)的服務(wù)器配置都不高，SGA也比較小，因此_kgl_bucket_count默認(rèn)還是1。用戶經(jīng)常出現(xiàn)bucket擴(kuò)容而引發(fā)的系統(tǒng)幾乎HANG死的情況，引起系統(tǒng)卡頓十多分鐘甚至更長。那時(shí)候這個(gè)數(shù)據(jù)庫每個(gè)月都會(huì)進(jìn)行重啟維護(hù)以避免共享池 碎片嚴(yán)重而引發(fā)的嚴(yán)重系統(tǒng)問題。

不過自從這個(gè)制度制定后，共享池碎片導(dǎo)致ORA-4031的問題解決了。不過重啟后過一段時(shí)間就會(huì)一定出現(xiàn)一次卡頓，隨后就基本上就不再出現(xiàn)了。這個(gè)問題十分詭異，我查了很久都沒有找到問題的根因，于是只能開了SR。經(jīng)過Oracle原廠排查，定位到了這個(gè)問題 。將該參數(shù)改為2之后，就沒有出現(xiàn)過這個(gè)問題。客戶的領(lǐng)導(dǎo)也是一個(gè)技術(shù)達(dá)人，他覺得既然這個(gè)問題存在，那么把這個(gè)參數(shù)再設(shè)大一點(diǎn)不是更加安全。于是我?guī)退麄冋{(diào)整了參數(shù)。結(jié)局很悲催，當(dāng)時(shí)的數(shù)據(jù)庫版本中這個(gè)參數(shù)設(shè)置得過大會(huì)觸發(fā)一個(gè)BUG，高負(fù)載時(shí)引發(fā)實(shí)例宕機(jī)。這個(gè)宕機(jī)問題很難定位，過了好久才被真正定位出來。