怎麼找出解析失敗的sql

原創如有錯誤請指出

本文由我和公司同事問心共同測試分析完成。

很多時候我們會有這樣一個誤區，語法錯誤或者物件不存在應該在語法語義檢查這個步驟就結束了，怎麼還會存在共享池裡面呢？帶著這個幾個問題我們做幾個簡單的測試。

我們先了解下什麼是解析失敗的 SQL？
1、SQL語法錯誤
2、訪問的物件不存在
3、沒有許可權

那麼怎麼證明有哪些解析失敗的SQL
我們知道 SQL 語句必須至少是一個父遊標一個子遊標存在的，當然生產中很多情況下都是一父多子的情況。
父遊標與子游標結構是一樣的，區別在於sql解析相關資訊儲存在父遊標對應的heap 0中，而sql的執行計劃等資訊儲存在子
遊標對應的庫快取物件heap 6記憶體空間中。另外父遊標的 heap 0中儲存著子游標的控制程式碼地址。如果解析錯誤的SQL在共
享池中儲存的話那麼必然要產生一個父遊標然後父遊標裡面儲存的有相關的解析資訊，但是子游標的？既然解析失敗那麼就
沒有產生執行計劃。
則利用這一點可以找到解析失敗的語句。
父遊標控制程式碼對地址可以在 x$kglob 檢視中查詢到，KGLHDPAR=KGLHDADR 的記錄為父遊標，
而KGLHDPAR<>KGLHDADR為子游標

X$KGLOB

該檢視定義為 [K]ernel[G]eneric [L]ibrary Cache Manager
KGLHDADR RAW(4|8) Address of kglhd for this object
可以看到：
KGLOBHD0 RAW(4|8) Address of heap 0 descriptor
KGLOBHD6 RAW(4|8) Address of heap 6 descriptor

SQL> select * from scott.emp;
SQL> col kglnaobj for a50;
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where KGLNAOBJ='select * from scott.emp';

KGLNAOBJ                                           KGLNATIM            KGLHDPAR         KGLHDADR         KGLOBHD0         KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp                            2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758
select * from scott.emp                            2017-07-07 14:54:52 0000000096AE88B0 0000000096AE88B0 0000000095871858 00


x$kglcursor_child_sqlid (只包含子游標資訊)
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglcursor_child_sqlid where KGLNAOBJ='select * from scott.emp';

KGLNAOBJ                                           KGLNATIM            KGLHDPAR         KGLHDADR         KGLOBHD0         KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp                            2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758

0000000096AE88B0 為select * from scott.emp; 父遊標控制程式碼地址，00000000958B9A40為子游標控制程式碼地址
子游標heap 6（KGLOBHD6）的地址為000000007713C758，控制程式碼中儲存的也就是執行計劃相關的資訊。
通過以上測試我們很容易找到sql的父遊標的控制程式碼還有子游標的控制程式碼在記憶體中的地址。

下面做另外一個簡單的測試解析錯誤的SQL是否有父遊標和子游標生成。
SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where KGLNAOBJ='select * from test';

KGLNAOBJ                                           KGLNATIM            KGLHDPAR         KGLHDADR         KGLOBHD0         KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ----------------                    ----------------
select * from test                                 2017-07-07 15:06:28 0000000097DDC190 0000000097F941F8 00               00
select * from test                                 2017-07-07 15:06:28 0000000097DDC190 0000000097DDC190 000000009E035698 00
SQL> select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglcursor_child_sqlid where KGLNAOBJ='select * from test';

no rows selected
可以看到沒有子游標生成，因為該SQL執行錯誤不會有執行計劃相關資訊。
從x$kglob 也可以查到 kglobhd0、kglobhd6 都為空(NULL)。
在 x$kglcursor_child 檢視也查不到任何資訊的，v$sql v$sqlare 類似的檢視也就查不到解析錯誤的 SQL 了。

關於解析失敗的SQL還是需要獲取latch，其實從上面的測試已經證明了還是要獲取 shared pool 的 latch的，因為生成了父遊標。
通過以上測試說明解析失敗的sql只生成了父遊標，而沒有生成子游標和執行計劃資訊。

也可以用一下sql查出當前資料庫中所有解析失敗的sql
select kglnaobj,kglnatim,kglhdpar,kglhdadr,KGLOBHD0,KGLOBHD6 from x$kglob where kglhdpar<>kglhdadr
and  KGLOBHD6='00' and KGLOBHD0='00' order by kglnatim desc;

從整個過程來看即使解析失敗父遊標是需要分配空間的，如果沒有使用繫結變數的情況下需要大量的分配
記憶體空間來儲存這些解析失敗語句的父遊標，它不僅會持有latch:libary cache而且會持有latch:shared
pool.

最後猜測一下：

KGLNAOBJ                                           KGLNATIM            KGLHDPAR         KGLHDADR         KGLOBHD0         KGLOBHD6
-------------------------------------------------- ------------------- ---------------- ---------------- ---------------- ----------------
select * from scott.emp                            2017-07-07 14:54:52 0000000096AE88B0 00000000958B9A40 0000000096AE85D8 000000007713C758

SQL> select ksmchptr,ksmchcom,ksmchcls,ksmchsiz from x$ksmsp where KSMCHPAR='0000000096AE85D8';

KSMCHPTR         KSMCHCOM                                         KSMCHCLS                   KSMCHSIZ
---------------- ------------------------------------------------ ------------------------ ----------
000000007713AFD8 KGLH0^31fa0cc                                    recr                           4096（chunk 大小）

如上：
KGLHDADR:應該為整個遊標結構體(控制程式碼)的虛擬記憶體地址其地址為00000000958B9A40
KGLHDPAR:為父遊標構體(控制程式碼的)的虛擬記憶體地址其地址為0000000096AE88B0
而父遊標的KGLHDPAR和KGLHDADR相等，子游標KGLHDPAR為父遊標構體(控制程式碼的)的虛擬記憶體地址，KGLHDADR為自己的
遊標構體(控制程式碼的)的虛擬記憶體地址
在這個結構體中有一根指標指向void* p 指向heap 0 ds描述符的記憶體空間，虛擬記憶體地址為0000000096AE85D8

ds描述符:應該也是一個結構體其中又有一根指標void* p 指向heap 0實際的虛擬記憶體地址為000000007713AFD8
那麼heap0實際的地址為000000007713AFD8
比如：
struct ds
{
  KSMCHCOM;
  KSMCHCLS;
  KSMCHSIZ;
  .........
void* p;
}
如果進行dump可以確實可以看到這根指標儲存確實儲存在記憶體中
作者微信:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2141728/，如需轉載，請註明出處，否則將追究法律責任。