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

--前一阵子，在做优化时遇到1个无法通过sql_id找到sql语句的情况：
http://blog.itpub.net/267265/viewspace-1749265/

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

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

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还是能查询到的。