本篇整理内容是本周四晚李華在“雲和恩墨大講堂”中授課的課件資料
故障分析 - library cache latch 競争案例分享
背景介紹
客戶的一套重要生産系統，出現了性能問題。這個問題涉及的資訊如下：
月底時候資料庫主機的 CPU 使用率長期在100%左右。
資料庫中出現大量的 latch: library cache 競争
系統概況
該系統為 OLAP OLTP 混合系統,平時為交易型資料庫。每個網點實時資料上傳,月底會有統計類報表産生。
以下為資料庫負載曲線，可以看到在月底複雜急劇上升，導緻業務不能正常操作。
以下為故障時間點部分 AWR 截圖。
從 LOAD PROFILE 看目前資料庫每秒有158次的硬解析，總的解析在1082次。
這個時間點的 TOP 5 等待事件中 latch: library cache 與 kksfbc child completion 排在前列，library cachelatch 占到将近有 70%。
Latch: Oracle 用于控制記憶體并發的串行鎖機制
共享池 latch 競争一般導緻的原因有以下集中:
- literal SQL 所謂的 literalSQL 就是沒用使用綁定變量值的 SQL 比如 select * from enmo where id=100;
- 硬解析比如一個新執行的 SQL 沒有在共享池中,那麼就要經曆一個硬解析的過程，關于過程這裡就不在多講
- SQL 不能共享，不能共享的原因有很多比如沒有在同一個使用者下面執行
- SQL VERSION 大量高版本 SQL 也會導緻共享池的競争
- 另外就是主機出現大量換頁，比如在 AIX 環境下大量計算記憶體使用了 SWAP 會導緻類似的問題
- 還有就是查詢一些底層的視圖比如 x$ksmsp 在某些版本下高并發的系統中直接查詢這些視圖會出現大量的 latch 競争
- 還有就是 SGA 大量抖動或者模拟調整的時候也會導緻此問題
- Oracle 各個版本上也存在相關的 BUG 會導緻
根據以上幾點我們去分析到底此問題出現在什麼地方。
首先資料庫等待事件除了 library cache latch 之後就是 kksfbc
K[Kernel]K[Kompile]S[Shared]F[Find]B[Best]C[Child]
該函數用以在軟解析時找尋合适的子遊标，是否該故障是由于大量 VERSION COUNT 引起呢？
從這個時間點 AWR 來看沒有看到大量 version count 的SQL出現。
分析 latch 的時候 AWR 有一個非常重要的資料。
從 Latch Miss Source 的資料可以看到，絕大多數都是對于 shared pool latch 的 sleeps，
從 AWR Sleep 來看 shared pool 排在了第一位。從調用的函數來看都是發生在硬解析這個過程中。
以下為一些常見函數的功能:
Kghfrunp: KGH: Ask client to freeunpinned space
Kghdmp : x$ksmsp is a fixed table based onkgh metadata. The number of latch sleeps for 'kghdmp' will increaseif x$ksmsp if an installation selectsfrom this fixed table too frequently.
kghupr1 : un-pin recreatable
kghalo KGH: main allocation entry point
kghgex KGH: Get a new extent
kghalf KGH: Non-recoverably allocate afreeable chunk of memory
有很多函數這裡就不一一列舉。
目前現在也可以排除人為查詢底層視圖導緻的 latch 競争因為沒有看到相關函數出現，插播一個類似的案例。
像這種情況很明顯就是有人查詢了底層的視圖導緻的 shared pool 競争。
從主機最早的資訊來看也是沒有 SWAP 競争出現的。
SGA 沒有大量的 resize 也可以排除掉由于 SGA 元件抖動引起的。
從以上資訊，我們沒有找到想要的結果，那麼問題出現在哪裡。
把上面幾個原因都排除掉了，難道真是遇到 Oracle BUG 了麼。
有的時候分析問題會陷入一些誤區，比如一個資料庫出現大量的 latch 競争導緻會話飙升然後把 process 撐滿，從 time mode 裡面來看的話可以發現 95%都是花費在了連接配接上面,那麼到底是大量不正常的連接配接(比如連接配接洩漏)導緻了資料庫出現競争呢，還是資料庫出現問題導緻會話不能等了然後不停的重連導緻了問題呢。
從這個庫這個時間點的 time mode 可以發現 75%的 db time 都是花費在了解析上面，這也是沒有問題的因為這個時間點資料庫競争就出現在解析上面，但是為什麼其中有 38%的 db time 發生在解析失敗上面呢，也就是總共解析的一般時間都是錯誤的解析。硬解析隻有5%左右。
我們來看一張正常時間點的 time mode 。
從這個趨勢圖庫看到解析失敗一直是跟着硬解析的次數而增加，并且每天都在上班之後開始發生。
資料庫正常時間點硬解析也隻有不到 5%左右，也就是硬解析沒有大的變化，但是解析失敗确認翻了幾倍。是什麼原因導緻這麼多的解析失敗呢？另外解析失敗的 SQL 是否會導緻大量 latch 競争？解析失敗的 SQL 是否會在共享池中存儲？怎麼查詢到解析失敗的 SQL？
很多時候我們會有這樣一個誤區，既然文法錯誤或者對象不存在應該在文法語義檢查這個步驟就挂了怎麼還好存在共享吃裡面呢？帶着這個幾個問題我們做幾個簡單的測試。
我們先了解下什麼是解析失敗的 SQL。
那麼怎麼證明就是解析失敗的 SQL 存在共享池中并且在解析的時候持有 library cache latch 呢？
做下面測試之前我們先回顧一個 Oracle 一些基本概念。
Library cache 是 shared pool 中的一塊記憶體區域，主要作用就是緩存執行過的 SQL 語句所對應的執行計劃資訊等資訊。當同樣的 SQL 再次執行時候可以直接利用已經緩存的相關對象不需要再從頭解析。
Library cache 對象句柄是以 hashtable 的方式存儲的，存儲方式如下圖:
當 sql 執行時候，首先會對 sql 文本進行 hash 運算然後根據 hash 值去相關 hash bucket 中周遊，如果找到了就直接用該 sql 緩存的執行計劃等，如果找不到則從頭解析，并把解析後執行計劃等緩存在 hash bucket 中。
下面這幾張圖檔展示了一個 SQL 解析的過程。
SQL 的記憶體結構
我們知道 SQL 語句必須至少是一個父遊标一個子遊标存在的，當然生産中很多情況下都是一父多子的情況。
父遊标與子遊标結構是一樣的，差別在于 sql 文本存儲在父遊标對應的對象句柄中，而 sql 的執行計劃等資訊存儲在子遊标對應的庫緩存對象句柄 heap 6 中。另外父遊标的 heap 0 中存儲着子遊标的句柄位址。如果解析錯誤的 SQL 在共享池中存儲的話那麼必然要産生一個父遊标然後父遊标裡面存儲的有 SQL 文本之類的資訊，但是子遊标的？既然解析失敗那麼就沒有産生執行計劃。
關于 heap 0 中資訊可以參考如下圖：
父遊标句柄對位址可以在 x$kglob 視圖中查詢到，KGLHDPAR=KGLHDADR 的記錄為父遊标
X$KGLOB
該視圖定義為 [K]ernel[G]eneric [L]ibrary Cache Manager
KGLHDADR RAW(4|8) Address of kglhd for this object
該位址 000007FF11937C90 為 select * from enmo SQL 的父遊标的句柄位址。
可以看到：
KGLOBHD0 RAW(4|8) Address of heap 0 descriptor
KGLOBHD6 RAW(4|8) Address of heap 6 descriptor
上面查到的就是該 SQL 父遊标的資訊，父遊标的 kglobhd0 的位址為 0000000075489AE8
該句柄位址記錄的資訊很多包含了子遊标的資訊。
找下該 SQL 子遊标的資訊：
子遊标 heap 6 的位址為 000000007625FBF8 句柄中存儲的也就是執行計劃相關的資訊。
通過以上測試我們很容易找到 sql 的父遊标的句柄還有子遊标的句柄在記憶體中的位址。
下面做另外一個簡單的測試解析錯誤的 SQL 是否有父遊标還有子遊标生成。
可以看到是可以查詢到資訊的，也就是有父遊标的句柄為 00000000754453B8 heap 0 的位址為 0000000075485620.
可以看到是有錯誤的文本資訊的記憶體位址，但是子遊标呢？
可以看到是沒有子遊标産生的，因為該 SQL 執行錯誤不會有執行計劃相關資訊出現。
從 x$kglob 也可以查到 kglobhd0 kglobhd6 都為空。
在 x$kglcursor_child 視圖也查不到任何資訊的，v$sql v$sqlare 類似的視圖也就查不到解析錯誤的 SQL 了。
關于解析錯誤的 SQL 是否需要擷取 latch 其實從上面的測試已經證明了還是要擷取 shared pool 的 latch 的因為生成了父遊标。
回顧以下SQL 硬解析過程中需要擷取的latch.
首先持有 library cache lath，在 library cache 相關 hash bucket 中掃描已經緩存的對象句柄，查找是否有比對的父遊标，沒有找到釋放 library cache latch.
接着持有 library cache latch 然後不釋放情況下持有 shared pool latch 從 shared pool 中申請配置設定記憶體成功後是否 shared pool latch 再是否 library cache latch.
還以上面那個錯誤的 SQL為例做一個簡單的測試。
首先擷取 library cache latch 然後運作 sql 查詢。
這個時候會話已經 hang 了。
怎麼找到解析失敗的 SQL？
- 通過關聯 x$kglcursor x$kglcursor_child_sqlid 這兩個視圖是可以找到解析失敗的 SQL
- 通過使用 Oracle 10035 event 事件也是可以找到解析失敗的SQL
- 通過 oracle systemdump 也可以找到解析失敗 SQL
當然最後該問題定位到了相關解析失敗的 SQL，該 SQL 主要是在月底某一子產品批量跑的時候大量的執行，最後修改應用程式代碼解決了問題。
通過這個簡單的案例可以看到不規範的開發習慣給資料庫帶了嚴重的性能影響。像類似這種解析出錯的 SQL 在很多客戶核心系統中比比皆是但是由于種種原因不能及時去除類似的 SQL 最終将帶來災難性的影響。
問答環節
1. 問： SGA 抖動是什麼意思？
答：各個元件來回的增加減少，看下圖比較直覺一些。
2. 問：如果在不知道原理的情況下，直接查找失敗的故障時段的大量失敗 SQL ，是不是也能初步發現問題 ？
答：如果不使用10035 event 關于失敗的SQL很難找到，因為在資料層面除非查底層的視圖不然是查不到解析錯誤的SQL 的。
3. 問：把cursor_sharing這個值設定成SIMILAR能緩解這個問題嗎？
答：當然不能，因為這個是解析錯誤 這個參數可以說是對他沒有效果，cursor_sharing=SIMILAR 風險更大，其實這個庫最早的時候就是設定成 SIMILAR 的，調整EXACT之後稍微好一些，但是之前解析錯誤的 SQL一直沒有發現。
問：風險在于哪裡？
答：這個是這個庫cursor_sharing=SIMILAR 的時候的 version count，
有直方圖就是災難。