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