《深入解析：由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 最終將帶來災難性的影響.

原文出處——Oracle微信公眾號

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4519.html