二十年前搞Oracle運維的時候，被折騰得最厲害的是共享池的問題，ORA-4031絕對是DBA必須面對的，也是最束手無措的錯誤。很多DBA面試官也會問大量的共享池診斷與最佳化的問題，雖然他自己對很多問題的瞭解也不過如此。

今早的這篇文章的主體結構是昨天下班前寫出來的，今早做了一些補充就發出來了。因為昨天上午我一直在做D-SMART這個部分的最佳化設計，這篇文章實際上是我這一天工作的一些總結。

Oracle 10G以後有了SGA動態分配的能力，而且伺服器的記憶體也從MB級別進入到了VLM的級別，共享池和ORA-4031的問題也就見得少了。在D-SMART裡，針對ORA-4031的監控功能比較少，只提供了一些用於分析的工具，不過這幾年也很少能發揮作用。

最近一個客戶的資料庫因為遇到BUG導致了一個例項出現ORA-4031,必須重啟才能解決問題。使用者提出了針對ORA-4031問題能否加強監控與分析。我這幾天也一直在考慮這個問題。Oracle資料庫中最脆弱和最複雜的元件就是SHARED POOL，對SHARED POOL的監控一定要特別小心。十多年前給使用者做Oracle服務的時候也經常遇到採集SHARED POOL的資料的時候把資料庫例項HANG死的問題。我甚至養成了採集共享池資料的時候一定另外開好另外一個視窗，一旦有問題立馬殺掉採集的會話。

可能很多朋友開發的Oracle監控工具裡都有共享池監控的功能，他們也覺得監控共享池的手段是很豐富的，為什麼我們會把這件事搞得這麼複雜呢？

在D-SMART的共享池資料採集方面，我也是十分謹慎的，不希望因為監控工具設計的不慎而導致原本負載過高的資料庫例項被監控指令碼搞垮。在V2.2版本的D-SMART中，和SHARED POOL相關的指標都是透過比較穩妥的系統檢視採集的。如今要加強共享池資料的採集，首先想到的就是v$sgastat，因為Oracle的AWR也會採集這個檢視裡的資料。

為了確認訪問的檢視的風險，我們需要找出檢視訪問的基礎資料結構，如果需要大量掃描共享池，那麼就應該儘可能避免。透過下面的指令碼可以查詢相關資訊。

SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SGASTAT';

可以看出，GV$SGASTAT的基礎檢視是x$ksmfs ,x$ksmss ,x$ksmls ,x$ksmjs ,x$ksmns, x$ksmstrs，這些基礎資料結構都是彙總KGH的資料的，本身不需要遍歷KGH，因此風險都不大。

比如ksmss儲存了共享物件的一些屬性，雖然不會在訪問該物件時持有shared pool的閂鎖，不過訪問過程中也會對共享池內的物件的變更產生影響。因此雖然我們可以比較安全的採集資料，不過也不適合過於頻繁。這樣的指標的採集，每個小時一次就可以了。

column indx heading "indx|indx num"

column kghlurcr heading "RECURRENT|CHUNKS"

column kghlutrn heading "TRANSIENT|CHUNKS"

column kghlufsh heading "FLUSHED|CHUNKS"

column kghluops heading "PINS AND|RELEASES"

column kghlunfu heading "ORA-4031|ERRORS"

column kghlunfs heading "LAST ERROR|SIZE"

select indx, kghlurcr, kghlutrn, kghlufsh, kghluops, kghlunfu, kghlunfs from sys.x$kghlu where inst_id = userenv('Instance')

對於監控共享池的情況來說，kghlu資料結構更為有效，可以十分詳細地檢視到共享池中的每個子池的統計資訊。

特別是kghlunfu/ kghlunfs這兩個欄位，顯示了每個子池出現的ORA-4031錯誤的次數以及最後一次分配錯誤所需分配的空間的大小。一般來說如果在某個子池中分配共享池空間失敗只是一個miss，此時會從另外一個池中分配，直到所有的子池中都無法分配空間，才會真正的出現FAILURE。因此ERRORS數量真正指出了共享池記憶體無法分配空間的情況。對該記憶體結構的監控可以比較準確地反映出共享池碎片產生的後果。不過這個資料結構的訪問也需要透過相關閂鎖，並且這個結構的訪問頻率要比前面所提的那些結構要頻繁。因此對該資料結構的採集依然不建議過於頻繁，一個小時採集一次已經足夠了。

為什麼這樣說呢？kghlu中的kghlusep指標是一個十分重要的指標，它指向了共享池LRU鏈上的一個關鍵位置，那個位置分割了共享池LRU鏈的冷熱區。當新的CHUNK要加入LRU鏈的時候，是新增在該指標左側的冷區尾部。而冷區中的CHUNK被多次訪問時會遷移到LRU鏈的熱端，以便於被重用。因此這個指標是訪問十分頻繁的，採集該結構的資料要格外謹慎。

x$kghlu經常被某些資料庫監控軟體用來監控共享池問題，不過頻繁的訪問這個資料結構還是會對資料庫產生影響的，特別是資料庫併發比較大，共享池存在效能問題的時候，如果過於頻繁的監控這個資料結構，可能會產生一些相當嚴重的問題。如果知道了這一點，我想大家應該理解為什麼我會對共享池的監控資料採集如此謹慎了。

col "avg size" format a30 truncate;

col siz format 999999999999

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ) /COUNT(KSMCHCLS) /1024)), '999,999.00')||'k' "AVG SIZE" FROM X$KSMSP GROUP BY KSMCHCLS;

實際上要分析shared pool的風險，上面的語句具有更好的效果，如果發現perm記憶體不斷增長，free的平均大小不斷下降，甚至低於4KB，那麼說明共享池出現了較大的碎片化風險。而下面的語句可以作更細緻的分析。

col sga_heap format a15

col size format a10

select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K') "size" ,count(*),ksmchcls Status, sum(ksmchsiz) Bytes from x$ksmsp where KSMCHCOM = 'free memory' group by ksmchidx, ksmchcls,'sga heap('|| KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

這條SQL可以採集到共享池中free記憶體的詳細情況，如果較大的heap比較少時，共享池的碎片化就很嚴重了。

似乎我們可以直接對x$ksmsp直接做採集，從而獲得對共享池分析的更有效的資料。不過真的如此嗎？我們如果看一下x$ksmsp的實際結構，就會明白為什麼我們不想把這個採集放到自動化採集的指令碼中，更好的採集共享池的資訊了。

我們可以看到ksmsp實際上指向了一個kghds的連結串列，而這個連結串列實際上是指向真實的heap鏈，對x$ksmsp的統計實際上會遍歷heap連結串列，對於共享池很大，並且共享池併發訪問很重，特別是共享池存在效能問題的場景，這種訪問無疑會加重共享池的負擔，甚至成為壓垮駱駝的最後一根稻草。如果這種採集放到不受控的自動化採集中去，那可能會帶來不可知的影響。因此這種分析我們只是在手工點選的工具中提供，而不會做成自動化採集的一部分。

監控與診斷實際上也是一種運維知識，開發監控與診斷工具，產品經理中應該有資深的運維專家，僅僅依靠高水平的研發人員是開發不出一套真正高水平的運維監控與診斷工具的。而對於一些比較脆弱的資料庫模組的監控採集，也需要十分謹慎的做設計，否則監控軟體會成為偽裝成天使的惡魔。

從如何更好的監控Oracle共享池談起

相關文章