怎么找出解析失败的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可以确实可以看到这根指针存储确实存储在内存中
作者微信: