我们知道使用10049 event可以跟踪语句执行过程中在library cache对象上产生的lock和pin的动作，但此方法仅在Oracle 10g版本下有效，11g下另有他法。
先来回顾一下10g里是怎么做的
//////////////////////////
// ORACLE 10gR2 下的测试
//////////////////////////
###创建测试表，执行测试SQL语句
drop table t2;
create table system.t2 as select * from all_users;
select * from system.t2 where user_id<50;
###获取语句hash value，转成16进制
select to_char(hash_value,'xxxxxxxxxx') from v$sql where sql_text like 'select * from system.t2 where user_id<%';
TO_CHAR(HAS
-----------
200eeb23
0xEB23|(0x2000+0x0010+0x0020)=0xEB232030=3944947760
注：
0xEB23是sql语句hash value转换成16进制以后的低4位数
0x2000表示"DUMP BY HASH VALUE"
0x0010表示"trace lock operations"
0x0020表示"trace pin operations"
###使用10049对语句进行trace
oradebug setmypid
oradebug event 10049 trace name context forever,level 3944947760;
Session altered.
select * from system.t2 where user_id<50;
oradebug tracefile_name
/oracle/app/oracle/admin/pboss/udump/pboss1_ora_2093188.trc
### .trc文件输出
cat /oracle/app/oracle/admin/pboss/udump/pboss1_ora_2093188.trc
*** 2016-04-26 10:53:00.346
*** ACTION NAME:() 2016-04-26 10:53:00.339
*** MODULE NAME:(sqlplus@qb550135 (TNS V1-V3)) 2016-04-26 10:53:00.339
*** SERVICE NAME:(SYS$USERS) 2016-04-26 10:53:00.339
*** SESSION ID:(1713.37581) 2016-04-26 10:53:00.339
KGLTRCLCK kglget hd = 0x7000001a3c2c860 KGL Lock addr = 0x7000001b2ad5800 mode = N <---因为本次是软解析所以输出较少我们再看一下硬解析的情况
###清空shared_pool后再次进行10049 trace
alter system flush shared_pool;
oradebug setmypid
oradebug event 10049 trace name context forever,level 3944947760;
Session altered.
select * from system.t2 where user_id<50;
oradebug tracefile_name
/oracle/app/oracle/admin/pboss/udump/pboss2_ora_2445438.trc
### .trc文件输出
cat /oracle/app/oracle/admin/pboss/udump/pboss2_ora_2445438.trc
*** SESSION ID:(2137.39198) 2016-04-26 11:12:04.699
KGLTRCLCK kglget hd = 0x7000001996e6eb8 KGL Lock addr = 0x7000001b00d2b38 mode = N
KGLTRCPIN kglpin hd = 0x7000001996e6eb8 KGL Pin addr = 0x7000001c0b81780 mode = X
KGLTRCPIN kglpndl hd = 0x7000001996e6eb8 KGL Pin addr = 0x7000001c0b81780 mode = X
KGLTRCLCK kglget hd = 0x700000153b8b5f8 KGL Lock addr = 0x7000001be110888 mode = N
KGLTRCPIN kglpin hd = 0x700000153b8b5f8 KGL Pin addr = 0x7000001b1204900 mode = X
KGLTRCPIN kglpndl hd = 0x700000153b8b5f8 KGL Pin addr = 0x7000001b1204900 mode = X
KGLTRCLCK kgllkdl hd = 0x700000153b8b5f8 KGL Lock addr = 0x7000001be110888 mode = N
KGLTRCLCK kgllkdl hd = 0x7000001996e6eb8 KGL Lock addr = 0x7000001b00d2b38 mode = N
###在x$kglob里查看上述handle address所代表的library cache里的对象
col KGLNAOWN format a10
col KGLNAOBJ format a58
col KGLHDOBJ format a25
set linesize 160
select kglhdadr,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr=hextoraw(upper('7000001996e6eb8'));
KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------- ---------------------------------------------------------- ---------- -------------------------
07000001996E6EB8 07000001996E6EB8 select * from system.t2 where user_id<:"SYS_B_0" 537848611 07000001B16EE9C8
select kglhdadr,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ from x$kglob where kglhdadr=hextoraw(upper('700000153b8b5f8'));
KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------- ---------------------------------------------------------- ---------- -------------------------
0700000153B8B5F8 07000001996E6EB8 select * from system.t2 where user_id<:"SYS_B_0" 537848611 07000001A2B80210
其中07000001996E6EB8是父游标的handle address、0700000153B8B5F8是子游标的handle address