[20241105]跟踪library cache lock library cache pin使用gdb(11g)2.txt
--//前一段时间写的使用gdb跟踪library cache lock/library cache pin的脚本。
--//我看过以前的笔记，当时测试过链接https://nenadnoveljic.com/blog/library-cache-lock-debugger/,我的测试在11g是失败.
--//今天有空再次尝试,确实不成功,不过有了前面测试的经验,可以很容易写成11g的gdb监测脚本.
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试gdb脚本：
--//我直接给出gdb脚本,一些细节忽略,主要讲解比较麻烦,就是在gdb下设置kgllkal,kglpnal断点.然后使用info register查看寄存器内
--//容,然后获取该执行语句的sql_id,hash_value值,继续退出,然后执行:
@ sharepool/shp4 <sql_id> 0
--//然后查看KGLHDADR的值,对比就知道那个寄存器保存的是对象句柄地址,测试结果对应的寄存器是rsi,至于mode我仅仅猜测是寄存器
--//rdx.这样稍微改改11g的监测脚本就可以写成.
--//里面的偏移量很容易确定,直接opeek 地址 长度 1,看看偏移地址在那里就可以了,11g我的测试结果在0x1a8 = 424.
$ cat lkpn11g.gdb
set pagination off
#set print repeats 0
#set print elements 0
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk = 0
set $pn = 0
set $lock = 0
#break kgllkal if $rdx==3
#break kgllkal if ( $rdx==3 && $rsi==0x00000000670C9E58 )
#break kgllkal if $rsi==0x00000000670C9E58
break kgllkal
commands
silent
printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
#break kglpnal if $rcx==3
break kglpnal
commands
silent
printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rsi ,$rdx
echo kglnaobj address:
x/s $rsi+0x1a8
c
end
3.测试：
--//session 1:
SCOTT@book> @ spid
==============================
SID : 18
SERIAL# : 44459
PROCESS : 65221
SERVER : DEDICATED
SPID : 65222
PID : 25
P_SERIAL# : 96
KILL_COMMAND : alter system kill session '18,44459' immediate;
PL/SQL procedure successfully completed.
--//执行desc dept以及Select * from dept where deptno=20;(开头S大小)多次避免递归调用。
--//window 1:
$ rlgdb -f -p 65222 -x lkpn11g.gdb
..
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0
Breakpoint 1 at 0x983da94
Breakpoint 2 at 0x9839f5c
--//session 1:
SCOTT@book> select * from dept where deptno=12;
no rows selected
--//window 1:
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8: "select * from dept where deptno=12"
kglpnal count 01 -- handle address: 000000007bcb6f50, mode: 2 kglnaobj address:0x7bcb70f8: "select * from dept where deptno=12"
kgllkal count 02 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 03 -- handle address: 000000007c19ee50, mode: 2 kglnaobj address:0x7c19eff8: "2710e6f2 1d05c1f$BUILD$"
--//怎么中间出现1个空格。~~~~~~~~~~~~~
kgllkal count 04 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8: ""
kglpnal count 02 -- handle address: 000000007cb51930, mode: 3 kglnaobj address:0x7cb51ad8: ""
kgllkal count 05 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 06 -- handle address: 000000007bcec280, mode: 1 kglnaobj address:0x7bcec428: "fa4ab910ef98d2aa2710e6f21d05c1fChild:0"
kglpnal count 03 -- handle address: 000000007bcec280, mode: 3 kglnaobj address:0x7bcec428: "fa4ab910ef98d2aa2710e6f21d05c1fChild:0"
kgllkal count 07 -- handle address: 000000007bcfc0a0, mode: 1 kglnaobj address:0x7bcfc248: "SCOTT"
kgllkal count 08 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 09 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
kglpnal count 04 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
kgllkal count 10 -- handle address: 000000007da13e58, mode: 1 kglnaobj address:0x7da14000: "\220\021"
kgllkal count 11 -- handle address: 000000007ef0a260, mode: 2 kglnaobj address:0x7ef0a408: "ICOL$SYS\bz骪177"
kglpnal count 05 -- handle address: 000000007ef0a260, mode: 2 kglnaobj address:0x7ef0a408: "ICOL$SYS\bz骪177"
--//以上第1次执行,有2个空是子游标句柄,对象内容在父游标句柄里面,有时候取到乱码也是正常的.
kgllkal count 12 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8: "select * from dept where deptno=12"
kgllkal count 13 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8: ""
kgllkal count 14 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
kglpnal count 06 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
--//以上第2次执行
kgllkal count 15 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8: "select * from dept where deptno=12"
kgllkal count 16 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8: ""
--//以上第3次执行
--//以上第4次执行,没有任何输出,光标已经cache.
SYS@book> @ sharepool/shp4 2f476y80x0r0z 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007CB51930 000000007BCB6F50 select * from dept where deptno=12 0 0 0 000000007CA23A58 000000007C8693C0 4528 12144 3067 19739 19739 30432287 2f476y80x0r0z 0
parent handle address 000000007BCB6F50 000000007BCB6F50 select * from dept where deptno=12 0 0 0 000000007CB39AD0 00 4720 0 0 4720 4720 30432287 2f476y80x0r0z 65535
--//下划线的对象有一点点奇怪中间有空格.
SYS@book> @ sharepool/shp4z 000000007c19ee50 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 000000007C19EE50 000000007C19EE50 $BUILD$.2710e6f2 1d05c1f 0 0 0 00 00 0 0 0 0 0 438406682 0
--//hash_value=438406682
SYS@book> select * from v$db_object_cache where hash_value=438406682
2 @pr
==============================
OWNER : $BUILD$
NAME : 2710e6f2 1d05c1f
DB_LINK :
NAMESPACE : SQL AREA BUILD
TYPE : CURSOR
SHARABLE_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 101914
INVALIDATIONS : 0
HASH_VALUE : 438406682
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 1
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : 6837047da7fa359a5549f81b1a218e1a
PL/SQL procedure successfully completed.
--//才发现11g下v$db_object_cache视图显示的字段太少。
SYS@book> select name,dump(name ,16) c70 from v$db_object_cache where hash_value=438406682;
NAME C70
---------------------------------------- ----------------------------------------------------------------------
2710e6f2 1d05c1f Typ=1 Len=16: 32,37,31,30,65,36,66,32,20,31,64,30,35,63,31,66
--//确实有1个空格.0x20.
$ sql_idz.sh "select * from dept where deptno=12\0" 3
sql_text = select * from dept where deptno=12\0
full_hash_value(16) = FA4AB910EF98D2AA2710E6F201D05C1F
xxxxx_matching_signature(10) = 2815003694193466399 or 21261747767903018015
hash_value(10) = 30432287 or hash_value(16) = 01D05C1F
sql_id(32) = 2f476y80x0r0z
sql_id(32) = 2f476y80x0r0z
sql_id(32) = 2f476y80x0r0z
--//对象2710e6f2 1d05c1f$BUILD$的来源是sql语句的FULL_HASH_VALUE的16进制的后16位2710E6F201D05C1F,分开2部分是
--//2710E6F2,01D05C1F,oracle设计有点奇葩,01D05C1F前面的0变成空格吗?看看FULL_HASH_VALUE计算是否正确.
--//kgllkal count 06 -- handle address: 000000007bcec280, mode: 1 kglnaobj address:0x7bcec428: "fa4ab910ef98d2aa2710e6f21d05c1fChild:0"
--//full_hash_value(16) = FA4AB910EF98D2AA2710E6F201D05C1F
fa4ab910ef98d2aa2710e6f21d05c1f
fa4ab910ef98d2aa2710e6f201d05c1f
--//对象fa4ab910ef98d2aa2710e6f21d05c1fChild:0 也是丢失0.
SYS@book> @ nmsp 'SQL AREA BUILD' -1
@ nmsp table -1
@ nmsp '' 74 or @ nmsp '' 0x4a|x4a
KGLSTDSC KGLSTIDN KGLSTIDN_HEX
---------------------------------------------------------------- ---------- ------------
SQL AREA BUILD 82 52
--//命名空间82，16进制0x52.
$ sql_idz.sh '2710e6f2 1d05c1f.$BUILD$\x52\0\0\0' 3
sql_text = 2710e6f2 1d05c1f.$BUILD$\x52\0\0\0
full_hash_value(16) = 6837047DA7FA359A5549F81B1A218E1A
xxxxx_matching_signature(10) = 6145715961809964570 or 24592460035519516186
hash_value(10) = 438406682 or hash_value(16) = 1A218E1A
sql_id(32) = 5akgs3cd233hu
sql_id(32) = 5akgs3cd233hu
sql_id(32) = 5akgs3cd233hu
--//确实使用空格计算的.full_hash_value(16) = 6837047DA7FA359A5549F81B1A218E1A与前面的使用如下语句的查询结果一致。
--//select * from v$db_object_cache where hash_value=438406682
kgllkal count 73 -- handle address: 000000007be33760, mode: 1 kglnaobj address:0x7be33908: "select * from dept where deptno=15"
kglpnal count 29 -- handle address: 000000007be33760, mode: 2 kglnaobj address:0x7be33908: "select * from dept where deptno=15"
kgllkal count 74 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 75 -- handle address: 000000007cab2918, mode: 2 kglnaobj address:0x7cab2ac0: " c3ba9c9ee26d7a6$BUILD$" --//空格再次出现。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kgllkal count 76 -- handle address: 000000007db6e0d0, mode: 1 kglnaobj address:0x7db6e278: "\220\021" -//乱码
kglpnal count 30 -- handle address: 000000007db6e0d0, mode: 3 kglnaobj address:0x7db6e278: "\220\021"
kgllkal count 77 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 78 -- handle address: 000000007c9cd2c8, mode: 1 kglnaobj address:0x7c9cd470: "f2afd967b55d9efac3ba9c9ee26d7a6Child:0"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kglpnal count 31 -- handle address: 000000007c9cd2c8, mode: 3 kglnaobj address:0x7c9cd470: "f2afd967b55d9efac3ba9c9ee26d7a6Child:0"
kgllkal count 79 -- handle address: 000000007bcfc0a0, mode: 1 kglnaobj address:0x7bcfc248: "SCOTT"
kgllkal count 80 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388: "bookSYS"
kgllkal count 81 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
kglpnal count 32 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178: "DEPTSCOTT"
$ sql_idz.sh "select * from dept where deptno=15\0" 3
sql_text = select * from dept where deptno=15\0
full_hash_value(16) = F2AFD967B55D9EFA0C3BA9C9EE26D7A6
xxxxx_matching_signature(10) = 881484836830107558 or 19328228910539659174
hash_value(10) = 3995522982 or hash_value(16) = EE26D7A6
sql_id(32) = 0sfx9t7r2dpx6
sql_id(32) = sfx9t7r2dpx6
sql_id(32) = sfx9t7r2dpx6
--//视乎11g下有一些不能理解的地方,开头的0变成空格.
--//f2afd967b55d9efac3ba9c9ee26d7a6 长度31也去掉0.
--//F2AFD967B55D9EFA0C3BA9C9EE26D7A6 长度32.
--//这里的0是字符'0',不是chr(0),oracle 11g为什么要这样设计变成空格呢？不理解。