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

[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為什麼要這樣設計變成空格呢？不理解。