[20150803]无法通过sql_id找到sql语句2.txt

--前一阵子，在做优化时遇到1个无法通过sql_id找到sql语句的情况：

--就是因为共享池太小，执行次数少，没到取样时间，已经从共享池清除。

--实际上还有容易被忽略的情况是lob字段。通过例子来说明：以前遇到等待事件enq HW - contention时也遇到过：

1.建立测试环境：

SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t (id number , text clob);

Table created.

SCOTT@test01p> insert into t values (1,'aaa');

1 row created.

SCOTT@test01p> commit ;

Commit complete.

SCOTT@test01p> select rowid,t.* from t;

ROWID                      ID TEXT ------------------ ---------- ----------------------------- AAAX/SAAJAAAATeAAA          1 aaa

--执行多次。

SCOTT@test01p> @rowid AAAX/SAAJAAAATeAAA     OBJECT       FILE      BLOCK        ROW DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- ----------------------------------------      98258          9       1246          0 9,1246               alter system dump datafile 9 block 1246

SCOTT@test01p> @10to16 98258

10 to 16 HEX   REVERSE16 -------------- ------------------ 0000000017fd2 0xd27f0100

--17fd2表示的是object_id.

SCOTT@test01p> @spid

       SID    SERIAL# SPID   C50 ---------- ---------- ------ --------------------------------------------------        355          7 4680   alter system kill session '355,7' immediate;

SYS@test01p> select sql_id,sql_text from V$OPEN_CURSOR where sid=355 and  sql_text like 'table%\_17fd2%' escape '\'; SQL_ID        SQL_TEXT ------------- ------------------------------------------------------------ 0dzargq9a6gg3 table_4_9_17fd2_0_0_0

SYS@test01p> select sys.dbms_sqltune_util0.sqltext_to_sqlid('table_4_9_17fd2_0_0_0'||chr(0)) c20 from dual;

C20 -------------------- 0dzargq9a6gg3

--很容易猜到这个与lob字段有关。

2.查询sql_id=0dzargq9a6gg3:

SYS@test01p> select * from v$sql where sql_id='0dzargq9a6gg3'; no rows selected

SELECT DECODE (kglhdadr,

               kglhdpar, '父游标句柄地址',                '子游标句柄地址')           text,        kglhdadr,        kglhdpar,        substr(kglnaobj,1,20),        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16,        kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20   FROM x$kglob WHERE kglobt03 = '&1';

TEXT           KGLHDADR         KGLHDPAR         SUBSTR(KGLNAOBJ,1,20)  KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16 KGLOBHS0+KGLOBHS6+KGLOBT16        N20

-------------- ---------------- ---------------- ---------------------- ---------------- ---------------- ---------- ---------- ---------- -------------------------- ---------- 子游标句柄地址 000007FF2668F1C8 000007FF57A166F0 table_4_9_17fd2_0_0_   000007FF2668F110 000007FF2BE09F08       4032       4032       3102                      11166      11166 父游标句柄地址 000007FF57A166F0 000007FF57A166F0 table_4_9_17fd2_0_0_   000007FF57A16638 00                     4072          0          0                       4072       4072

--不过这种情况查询查询x$kglob还是能查询到的。