深入解析：由SQL解析失敗看開發與DBA的效能之爭

深入解析：由SQL解析失敗看開發與DBA的效能之爭

---

---

李華

雲和恩墨高階技術顧問

編者注：在很多生產系統中，程式設計師經意不經意寫下的一條SQL都可能帶來效能上的巨大隱患，正確的、不正確的。而DBA就要不斷在這些問題中出生入死，本案例描述的那些不正確的SQL可能給我們帶來的麻煩，而這類錯誤SQL往往為大家所忽視。這樣的問題在最近的客戶案例中不斷湧現，在12c中同樣為我們帶來麻煩，而很多DBA可能並不瞭解，轉引這篇文章與大家為警示。

以下案例來自大講堂的一次分享，從這個案例中我們可以瞭解“錯誤的SQL”可能對資料庫產生的種種影響。如何找到這些錯誤的、解析失敗的SQL呢？我們先把方法列舉在這裡：

1. 透過關聯 x$kglcursor x$kglcursor_child_sqlid  檢視；

2. 透過使用 Oracle 10035 Event 事件可以找到解析失敗的SQL；

3. 透過 oracle systemdump 也可以找到解析失敗 SQL；

以下我們來看看這個精彩的案例分享。

背景介紹

客戶的一套重要生產系統，出現了效能問題。這個問題涉及的資訊如下：

月底時候資料庫主機的 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 競爭一般導致的原因有以下集中:

1. literal SQL 所謂的 literalSQL 就是沒用使用繫結變數值的 SQL 比如 select * from enmo where id=100;

2. 硬解析比如一個新執行的 SQL 沒有在共享池中,那麼就要經歷一個硬解析的過程，關於過程這裡就不在多講

3. SQL 不能共享，不能共享的原因有很多比如沒有在同一個使用者下面執行

4. SQL VERSION 大量高版本 SQL 也會導致共享池的競爭

5. 另外就是主機出現大量換頁，比如在 AIX 環境下大量計算記憶體使用了 SWAP 會導致類似的問題

6. 還有就是查詢一些底層的檢視比如 x$ksmsp 在某些版本下高併發的系統中直接查詢這些檢視會出現大量的 latch 競爭

7. 還有就是 SGA 大量抖動或者模擬調整的時候也會導致此問題

8. 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？

1. 透過關聯 x$kglcursor x$kglcursor_child_sqlid  這兩個檢視是可以找到解析失敗的 SQL

2. 透過使用 Oracle 10035 event 事件也是可以找到解析失敗的SQL

3. 透過 oracle systemdump 也可以找到解析失敗 SQL

 

當然最後該問題定位到了相關解析失敗的 SQL，該 SQL 主要是在月底某一模組批次跑的時候大量的執行，最後修改應用程式程式碼解決了問題。

透過這個簡單的案例可以看到不規範的開發習慣給資料庫帶了嚴重的效能影響。像類似這種解析出錯的 SQL 在很多客戶核心系統中比比皆是但是由於種種原因不能及時去除類似的 SQL 最終將帶來災難性的影響。

---

---

About Me

............................................................................................................................... ● 本文來自公眾號，若有侵權，請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。