[20241108]跟蹤library cache lock library cache pin使用gdb(11g)3.txt

[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已經釋放,另外寫一篇驗證自己的測試結果是否正確。.