[20241108]跟踪library cache lock library cache pin使用gdb(11g)3.txt
--//前一段时间写的使用gdb跟踪library cache lock/library cache pin的脚本。
--//我看过以前的笔记，当时测试过链接https://nenadnoveljic.com/blog/library-cache-lock-debugger/,我的测试在11g是失败.
--//今天有空再次尝试,确实不成功,不过有了前面测试的经验,可以很容易写成11g的gdb监测脚本.
--//前几天完成了11g下的脚本,有一个小问题,不能获取lock address,今天尝试完成它.
--//通过调用kglGetSO返回lock address/pin address.
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.测试:
--//session 1:
--//执行如下语句多次。
SCOTT@book> select * from dept where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
SCOTT@book> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
95129850 80baj2c2ur47u 0 102650 2852011669 5ab90fa 2024-11-08 08:59:46 16777220
--//session 2:
SYS@book> @ sharepool/shp4 80baj2c2ur47u 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 000000007D927640 000000007DB40C60 select * from dept where deptno=20 1 0 0 000000007D3CC960 000000007CAA5948 4528 12144 3067 19739 19739 95129850 80baj2c2ur47u 0
parent handle address 000000007DB40C60 000000007DB40C60 select * from dept where deptno=20 1 0 0 000000007C6565D8 00 4720 0 0 4720 4720 95129850 80baj2c2ur47u 65535
SYS@book> column KGLNAOBJ format a40
SYS@book> select KGLLKSNM sid ,KGLLKMOD,KGLLKREQ,KGLNAOBJ,KGLLKHDL,KGLLKADR,KGLLKUSE,KGLLKSES from x$kgllk where KGLNAOBJ='select * from dept where deptno=20';
SID KGLLKMOD KGLLKREQ KGLNAOBJ KGLLKHDL KGLLKADR KGLLKUSE KGLLKSES
---------- ---------- ---------- ---------------------------------------- ---------------- ---------------- ---------------- ----------------
1 1 0 select * from dept where deptno=20 000000007D927640 000000007DAF9380 0000000085C77E38 0000000085C77E38
1 1 0 select * from dept where deptno=20 000000007DB40C60 000000007D7182E0 0000000085C77E38 0000000085C77E38
--//KGLLKHDL 对应 父子游标的句柄地址，KGLLKADR表示lock地址。
----//session 1,退出重新登录,因为会话光标已经换成,实际上测试前可以设置session_cached_cursors,这样每次都是软解析.
SCOTT@book> alter session set session_cached_cursors=0;
Session altered.
SCOTT@book> @ spid
==============================
SID : 1
SERIAL# : 1811
PROCESS : 19892
SERVER : DEDICATED
SPID : 19893
PID : 24
P_SERIAL# : 137
KILL_COMMAND : alter system kill session '1,1811' immediate;
PL/SQL procedure successfully completed.
--//我的环境仅仅个人使用,重新登录sid还是1.
--//window 1:
$ 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
break kglGetSO
commands
silent
finish
end
$ rlgdb -f -p 19893 -x lkpn11g.gdb
...
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0
Breakpoint 1 at 0x983da94
Breakpoint 2 at 0x9839f5c
Breakpoint 3 at 0x9845840
--//session 1:
SCOTT@book> select * from dept where deptno=20;
--//挂起!!
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000007db40c60, mode: 1 kglnaobj address:0x7db40e08: "select * from dept where deptno=20"
0x000000000983db73 in kgllkal ()
(gdb) info regi
rax 0x7c174e28 2081902120
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
rbx 0x60004668 1610630760
rcx 0x99d6510 161309968
rdx 0x7c174e28 2081902120
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
rsi 0xc0f1b58 202316632
rdi 0x7d63f408 2103702536
rbp 0x7ffff3af33b0 0x7ffff3af33b0
rsp 0x7ffff3af3300 0x7ffff3af3300
r8 0x14 20
r9 0x0 0
r10 0xea0 3744
r11 0x3 3
r12 0xc0cc9e0 202164704
r13 0x7db40c60 2108951648
r14 0x1 1
r15 0x7ffff3af39e0 140737281735136
rip 0x983db73 0x983db73 <kgllkal+227>
eflags 0x246 [ PF ZF IF ]
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
fctrl 0x27f 639
fstat 0x420 1056
ftag 0xffff 65535
fiseg 0x0 0
fioff 0x961e00e 157409294
foseg 0x7fff 32767
fooff 0xf3af4160 -206618272
fop 0x0 0
mxcsr 0x1fa0 [ PE IM DM ZM OM UM PM ]
(gdb) c
Continuing.
kgllkal count 02 -- handle address: 000000007d927640, mode: 1 kglnaobj address:0x7d9277e8: ""
0x000000000983db73 in kgllkal ()
(gdb) c
Continuing.
--//session 2:
SYS@book> select KGLLKSNM sid ,KGLLKMOD,KGLLKREQ,KGLNAOBJ,KGLLKHDL,KGLLKADR,KGLLKUSE,KGLLKSES from x$kgllk where KGLNAOBJ='select * from dept where deptno=20';
SID KGLLKMOD KGLLKREQ KGLNAOBJ KGLLKHDL KGLLKADR KGLLKUSE KGLLKSES
---------- ---------- ---------- ---------------------------------------- ---------------- ---------------- ---------------- ----------------
1 1 0 select * from dept where deptno=20 000000007D927640 000000007D63F7E8 0000000085C77E38 0000000085C77E38
1 1 0 select * from dept where deptno=20 000000007DB40C60 000000007C174E28 0000000085C77E38 0000000085C77E38
--//注意看rax=0x7c174e28, 与KGLLKHDL=000000007DB40C60(父游标句柄)那行KGLLKADR=000000007C174E28相互对应。也就是返回后寄
--//存器rax的值就是返回lock address。好像rdx也能对上.
--//这样就可以知道调用kgllkal，再调用kglGetSO返回的地址是0x000000000983db73,在此设置断点，查看相应寄存器就可以知道lock address。
--//这样gdb脚本改写如下：
$ 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
#break kglGetSO
#commands
# silent
# finish
# end
break *0x000000000983db73
commands
silent
printf "kgllkal kglGetS0 lock address : %016x\n", $rax
c
end
--//重复测试:
--//session 2:
SYS@book> select KGLLKSNM sid ,KGLLKMOD,KGLLKREQ,KGLNAOBJ,KGLLKHDL,KGLLKADR,KGLLKUSE,KGLLKSES from x$kgllk where KGLNAOBJ='select * from dept where deptno=20';
SID KGLLKMOD KGLLKREQ KGLNAOBJ KGLLKHDL KGLLKADR KGLLKUSE KGLLKSES
---------- ---------- ---------- ---------------------------------------- ---------------- ---------------- ---------------- ----------------
1 1 0 select * from dept where deptno=20 000000007D927640 000000007D63F7E8 0000000085C77E38 0000000085C77E38
1 1 0 select * from dept where deptno=20 000000007DB40C60 000000007C174E28 0000000085C77E38 0000000085C77E38
--//window 1:
--//退出gdb界面,重新执行:
$ rlgdb -f -p 19893 -x lkpn11g.gdb
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0
Breakpoint 1 at 0x983da94
Breakpoint 2 at 0x9839f5c
Breakpoint 3 at 0x983db73
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000007db40c60, mode: 1 kglnaobj address:0x7db40e08: "select * from dept where deptno=20"
kglGetS0 lock address : 000000007c174e28
kgllkal count 02 -- handle address: 000000007d927640, mode: 1 kglnaobj address:0x7d9277e8: ""
kglGetS0 lock address : 000000007d63f7e8
--//完全能对上.剩下定位library cache pin address.实际上如法炮制.
--//注解 lkpn11g.gdb脚本的break kgllkal段落.
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
break kglGetSO
commands
silent
finish
end
--//session 1:
SCOTT@book> select * from dept where deptno=60;
--//window 1:
--//退出gdb界面,重新执行:
(gdb) c
Continuing.
0x000000000983db73 in kgllkal ()
(gdb) c
Continuing.
0x000000000984175e in kglllal ()
(gdb) c
Continuing.
kglpnal count 01 -- handle address: 000000007c53b830, mode: 2 kglnaobj address:0x7c53b9d8: "select * from dept where deptno=60"
0x000000000983a048 in kglpnal ()
--//这样就可以知道调用kglpnal，再调用kglGetSO返回的地址是0x000000000983a048,在此设置断点，查看相应寄存器就可以知道lock address。
--//注意一定要对应kglpna调用返回的值,前面2个不是.
$ 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
#break kglGetSO
#commands
# silent
# finish
# end
break *0x000000000983db73
commands
silent
printf "kglGetS0 return lock address : %016x %016x\n", $rax,$rdx
c
end
break *0x000000000983a048
commands
silent
printf "kglGetS0 return pin address : %016x %016x\n", $rax,$rdx
c
end
--//session 1:
SCOTT@book> select * from dept where deptno=32;
no rows selected
--//window 1:
--//退出gdb界面,重新执行:
Breakpoint 1 at 0x983da94
Breakpoint 2 at 0x9839f5c
Breakpoint 3 at 0x983db73
Breakpoint 4 at 0x983a048
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000007bea0990, mode: 1 kglnaobj address:0x7bea0b38: "select * from dept where deptno=32"
kglGetS0 return lock address : 000000007c175128 000000007c175128
kglpnal count 01 -- handle address: 000000007bea0990, mode: 2 kglnaobj address:0x7bea0b38: "select * from dept where deptno=32"
kglGetS0 return pin address : 000000007c175228 000000007c175228
kgllkal count 02 -- handle address: 000000007dbb0788, mode: 2 kglnaobj address:0x7dbb0930: "bookSYS"
kglGetS0 return lock address : 000000007c174e28 000000007c174e28
kgllkal count 03 -- handle address: 000000007c3e3ba8, mode: 2 kglnaobj address:0x7c3e3d50: "c40e5b0d7f1b5ad0$BUILD$"
kglGetS0 return lock address : 000000007c175228 000000007c175228
kgllkal count 04 -- handle address: 000000007cebdcb0, mode: 1 kglnaobj address:0x7cebde58: ""
kglGetS0 return lock address : 000000007d63f7e8 000000007d63f7e8
kglpnal count 02 -- handle address: 000000007cebdcb0, mode: 3 kglnaobj address:0x7cebde58: ""
kglGetS0 return pin address : 000000007c174e28 000000007c174e28
kgllkal count 05 -- handle address: 000000007dbb0788, mode: 2 kglnaobj address:0x7dbb0930: "bookSYS"
kglGetS0 return lock address : 000000007c175528 000000007c175528
kgllkal count 06 -- handle address: 000000007d932218, mode: 1 kglnaobj address:0x7d9323c0: "23bc01efc31eccb6c40e5b0d7f1b5ad0Child:0"
kglGetS0 return lock address : 000000007c5772e0 000000007c5772e0
kglpnal count 03 -- handle address: 000000007d932218, mode: 3 kglnaobj address:0x7d9323c0: "23bc01efc31eccb6c40e5b0d7f1b5ad0Child:0"
kglGetS0 return pin address : 000000007c5773e0 000000007c5773e0
kgllkal count 07 -- handle address: 000000007d1f57b8, mode: 1 kglnaobj address:0x7d1f5960: "SCOTT"
kglGetS0 return lock address : 000000007c5772e0 000000007c5772e0
kgllkal count 08 -- handle address: 000000007dbb0788, mode: 2 kglnaobj address:0x7dbb0930: "bookSYS"
kglGetS0 return lock address : 000000007c174e28 000000007c174e28
kgllkal count 09 -- handle address: 000000007d065910, mode: 2 kglnaobj address:0x7d065ab8: "DEPTSCOTT"
kglGetS0 return lock address : 000000007c175528 000000007c175528
kglpnal count 04 -- handle address: 000000007d065910, mode: 2 kglnaobj address:0x7d065ab8: "DEPTSCOTT"
kglGetS0 return pin address : 000000007c5773e0 000000007c5773e0
SYS@book> select * from x$kglpn where KGLPNSID=1;
no rows selected
--//pin address已经释放,另外写一篇验证自己的测试结果是否正确。.