[20240824]跟蹤library cache lock library cache pin使用gdb.txt

[20240824]跟蹤library cache lock library cache pin使用gdb.txt

--//這幾天一直想寫一個gdb指令碼實現這個功能,先開始自己嘗試,遇到一些問題,冷靜下來看了以前的學習筆記,網上查了相關連結,能找到
--//的資源很少:
--//https://nenadnoveljic.com/blog/tracing-library-cache-locks/
--//https://nenadnoveljic.com/blog/library-cache-lock-debugger/
--//https://nenadnoveljic.com/blog/library-cache-lock-object-name/
--//https://mvelikikh.blogspot.com/2021/02/tracing-library-cache-locks-using.html

--//記憶裡當時測試過連結https://nenadnoveljic.com/blog/library-cache-lock-debugger/,在11g失敗,不知道為什麼.
--//我當時想也許作者使用不是intel cpu,前面https://nenadnoveljic.com/blog/tracing-library-cache-locks/使用dtrace.
--//我個人不熟悉dtrace,實際上gdb也不熟悉，這類工具不做internal的研究基本很少使用。

--//我決定在21c下重複再次嘗試看看:

1.環境:
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.

create table t (n1 integer,n2 integer);
create index ix_t on t(n1,n2);
exec dbms_stats.gather_table_stats (null, 'T', cascade => true ) ;

2.測試:
$ cat lcl.gdb
break kgllkal if $rcx==3
commands 1
backtrace
p/x $rdx
finish
end
--//注：原始連結使用的是p/z $rdx，我測試報錯，修改為p/x $rdx，所以我猜測他使用環境可能不是intel cpu系列。

--//session 1:
SCOTT@book01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
153 14481 3573 DEDICATED 3575 93 3 alter system kill session '153,14481' immediate;

--//session 2:
$ rlgdb -f -p 3575 -x lcl.gdb

--//session 1:
SCOTT@book> alter index ix_t invisible ;

--//session 2:
Breakpoint 1 at 0x15367e90
(gdb) c
Continuing.

Breakpoint 1, 0x0000000015367e90 in kgllkal ()
#0 0x0000000015367e90 in kgllkal ()
#1 0x0000000015363ad0 in kglLock ()
#2 0x000000001535e178 in kglget ()
#3 0x00000000039d07d6 in kkdllk0 ()
#4 0x00000000039ee51e in kkdllppac0 ()
#5 0x000000000bbd0ea3 in aindrv ()
#6 0x0000000014efc7f2 in opiexe ()
#7 0x00000000151ca093 in opiosq0 ()
#8 0x0000000014f6f166 in kpooprx ()
#9 0x0000000014f6ca1b in kpoal8 ()
#10 0x0000000014eefdf8 in opiodr ()
#11 0x0000000015294359 in ttcpip ()
#12 0x00000000030025c2 in opitsk ()
#13 0x0000000003007920 in opiino ()
#14 0x0000000014eefdf8 in opiodr ()
#15 0x0000000002ffe2ab in opidrv ()
#16 0x0000000003e8bd15 in sou2o ()
#17 0x0000000000e91ae0 in opimai_real ()
#18 0x0000000003e9915c in ssthrdmain ()
#19 0x0000000000e91924 in main ()
$1 = 0x6e2bd378
~~~~~~~~~~~~~
0x0000000015363ad0 in kglLock ()

(gdb) c
Continuing.

Breakpoint 1, 0x0000000015367e90 in kgllkal ()
#0 0x0000000015367e90 in kgllkal ()
#1 0x0000000015363ad0 in kglLock ()
#2 0x000000001535e178 in kglget ()
#3 0x00000000039d07d6 in kkdllk0 ()
#4 0x00000000039ee60b in kkdllppac0 ()
#5 0x000000000bbd0ea3 in aindrv ()
#6 0x0000000014efc7f2 in opiexe ()
#7 0x00000000151ca093 in opiosq0 ()
#8 0x0000000014f6f166 in kpooprx ()
#9 0x0000000014f6ca1b in kpoal8 ()
#10 0x0000000014eefdf8 in opiodr ()
#11 0x0000000015294359 in ttcpip ()
#12 0x00000000030025c2 in opitsk ()
#13 0x0000000003007920 in opiino ()
#14 0x0000000014eefdf8 in opiodr ()
#15 0x0000000002ffe2ab in opidrv ()
#16 0x0000000003e8bd15 in sou2o ()
#17 0x0000000000e91ae0 in opimai_real ()
#18 0x0000000003e9915c in ssthrdmain ()
#19 0x0000000000e91924 in main ()
$2 = 0x68c3f220
~~~~~~~~~~~~~
0x0000000015363ad0 in kglLock ()
--//不知道為什麼當時失敗在11g,有機會重新測試看看.

SYS@book> select kgllkmod,kglnaobj from x$kgllk where kgllkhdl = upper('000000006e2bd378') ;
KGLLKMOD KGLNAOBJ
---------- ------------------------------
3 T

SYS@book> select kgllkmod,kglnaobj from x$kgllk where kgllkhdl = upper('0000000068c3f220') ;
KGLLKMOD KGLNAOBJ
---------- ------------------------------
3 IX_T

3.獲取KGLNAOBJ物件:
--//看了連結https://nenadnoveljic.com/blog/library-cache-lock-object-name/,他是透過handle address的偏移獲得物件名稱.
--//我自己也做了嘗試,我發現偏移量存在偏差對比作者的測試,我的測試多了8個偏移，作者的偏移量0x1c0.
--//最後生成gdb的測試指令碼,其中的分析過程跳過。
--//另外作者提到lock address從呼叫kglGetSO的返回獲得，感覺寫gdb比較困難，我放棄這部分內容。
--//與以前vage<oracle核心技術解密>書上的介紹有點不同.
--//https://nenadnoveljic.com/blog/tracing-library-cache-locks/
--//In order to close this gap I, first, examined the following two Oracle C functions on the release 19.6.0.0.200114:
--//kgllkal and kglGetSO.

--//kgllkal allocates a library cache lock. It receives the following arguments:

--//rdx: handle address
--//rcx: lock mode
--//注:rdx是handle address沒有問題,rcx是lock mode,我不知道是否正確,暫且認為這樣,我估計作者也是猜測獲得.

--//Further, it calls kglGetSO to allocate the library cache state object. kglGetSO returns the lock address.

--//有了以上的素材,最終修改跟蹤指令碼如下:

$ cat lkpn.gdb
set pagination off
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk = 0
set $pn = 0

#break kgllkal if $rcx==3
break kgllkal
commands
silent
printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rdx ,$rcx
echo kglnaobj address:
x/s $rdx+0x1c8
c
end

#break kglpnal if $rcx==3
break kglpnal
commands
silent
printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rdx ,$rcx
echo kglnaobj address:
x/s $rdx+0x1c8
c
end

#break kglGetSO
#commands
# silent
# printf "lock address : %016x\n", $r11
# c
# end

4.利用以上指令碼測試看看：
--//測試執行sql語句的情況看看.
--//session 1:
SCOTT@book01p> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
400 34954 6214 DEDICATED 6216 63 13 alter system kill session '400,34954' immediate;

--//Select * from dept where deptno=20; 執行多次,注意第1個字母大寫.減少遞迴,不然下面的輸出內容會更多.

--//session 2:
$ rlgdb -f -p 6216 -x lkpn.gdb
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-94.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
..
0x00007f6ffaf66480 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:81
/usr/src/debug/glibc-2.17-c758a686/sysdeps/unix/syscall-template.S:81:3374:beg:0x7f6ffaf66480
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020

--//session 1:
SCOTT@book01p> select * from dept where deptno=20;
--//注意第1個字母小寫

--//掛起,切換到session 2:
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960: "select * from dept where deptno=20"
kgllkal count 02 -- handle address: 0000000065f7a2e8, mode: 2 kglnaobj address:0x65f7a4b0: "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P"
kgllkal count 03 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418: ""
kglpnal count 01 -- handle address: 000000006c880250, mode: 3 kglnaobj address:0x6c880418: ""
kgllkal count 04 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300: "bookSYSCDB$ROOT"
kgllkal count 05 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"
kgllkal count 06 -- handle address: 0000000062ce3480, mode: 1 kglnaobj address:0x62ce3648: "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648: "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kgllkal count 07 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78: "SCOTTBOOK01P"
kgllkal count 08 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300: "bookSYSCDB$ROOT"
kgllkal count 09 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"
kgllkal count 10 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 03 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 11 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80: "5358706841214419813BOOK01P"
kglpnal count 04 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80: "5358706841214419813BOOK01P"
kgllkal count 12 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kglpnal count 05 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kgllkal count 13 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8: "13547376130454050250BOOK01P"
kglpnal count 06 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8: "13547376130454050250BOOK01P"
kgllkal count 14 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50: "4448762010415191240BOOK01P"
kglpnal count 07 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50: "4448762010415191240BOOK01P"
kgllkal count 15 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kglpnal count 08 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
--//第1次執行硬解析,後面的內容就是kglnaobj物件名稱.注意有2個沒有內容,這是因為是sql語句的的child handle address,應該從
--//parent handle address上取,為空很正常.
--//比如 bookSYSCDB$ROOT 實際上就是資料庫的例項名,不知道為什麼實際上oracle計算full_hash_value中間有小數點分割開的.
--//實際上計算使用book.SYS.CDB$ROOT\x<namespace_hex>\0\0\0字串.
SYS@book> @ nmsp.sql %instance% -1
KGLSTDSC KGLSTIDN NMSP_HEX
----------- -------- --------
DBINSTANCE 74 4a

$ sql_idz.sh 'book.SYS.CDB$ROOT\x4a\0\0\0' 3
sql_text = book.SYS.CDB$ROOT\x4a\0\0\0
full_hash_value(16) = B885BB910059A4D2FDBFC245A6411BA3 or b885bb910059a4d2fdbfc245a6411ba3
xxxxx_matching_signature(10) = -162197457164231773 or 18284546616545319843
hash_value(10) = 2789284771
sql_id(32) = gvgy28qm426x3
sql_id(32) = gvgy28qm426x3
sql_id(32) = gvgy28qm426x3
--//注:要使用單引號,因為裡面有$ROOT,使用雙引號會解析為變數.

SYS@book> @ sharepool/shp4 00000000701df138 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- -------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 00000000701DF138 00000000701DF138 SYS.book 0 0 0 00 00 0 0 0 0 0 2789284771 0
--//KGLNAHSH=2789284771,說明計算沒有問題.

SYS@book> @ kglob 0 2789284771
==============================
INST_ID : 1
OWNER : SYS
NAME : book
DB_LINK :
NAMESPACE : DBINSTANCE
TYPE : CURSOR
NAMESPACE_NUM : 74
NAMESPACE_HEX : 4a
SHARABLE_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 72611
INVALIDATIONS : 0
HASH_VALUE : 2789284771
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 1313195
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : b885bb910059a4d2fdbfc245a6411ba3
CON_ID : 1
CON_NAME : CDB$ROOT
ADDR : 00000000701DF138
EDITION :
SQL_ID :
OBJECT_STR : book.SYS.CDB$ROOT\x4a\0\0\0
PL/SQL procedure successfully completed.
--//FULL_HASH_VALUE : b885bb910059a4d2fdbfc245a6411ba3也能對上.

kgllkal count 16 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960: "select * from dept where deptno=20"
kgllkal count 17 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418: ""
kgllkal count 18 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 09 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
--//第2次執行軟解析,kglnaobj物件名稱後面出現亂碼很正常,我沒有判斷顯示長度.

kgllkal count 19 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960: "select * from dept where deptno=20"
kgllkal count 20 -- handle address: 000000006c880250, mode: 1 kglnaobj address:0x6c880418: ""
--//第3次執行快取游標.

--//第4次執行軟軟解析.沒有任何輸出....

5.補充一些解析:
kgllkal count 01 -- handle address: 000000006cf61798, mode: 1 kglnaobj address:0x6cf61960: "select * from dept where deptno=20"
kgllkal count 02 -- handle address: 0000000065f7a2e8, mode: 2 kglnaobj address:0x65f7a4b0: "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P"
--//e8ec445edab00042802d511305ab90fa數字實際上sql語句的full_hash_value,11g取的後16位,21c取的是全部32位.

$ sql_idz.sh 'select * from dept where deptno=20\0' 3
sql_text = select * from dept where deptno=20\0
full_hash_value(16) = E8EC445EDAB00042802D511305AB90FA or e8ec445edab00042802d511305ab90fa
xxxxx_matching_signature(10) = -9210616520761437958 or 9236127552948113658
hash_value(10) = 95129850
sql_id(32) = 80baj2c2ur47u
sql_id(32) = 80baj2c2ur47u
sql_id(32) = 80baj2c2ur47u
--//full_hash_value 完全能對上.

SYS@book> @ sharepool/shp4 0000000065f7a2e8 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 0000000065F7A2E8 0000000065F7A2E8 $BUILD$.e8ec445edab00042802d511305ab90fa 0 0 0 00 00 0 0 0 0 0 1635416785 0

SYS@book> @ kglob 0 1635416785
==============================
INST_ID : 1
OWNER : $BUILD$
NAME : e8ec445edab00042802d511305ab90fa
DB_LINK :
NAMESPACE : SQL AREA BUILD
TYPE : CURSOR
NAMESPACE_NUM : 82
NAMESPACE_HEX : 52
SHARABLE_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 31441
INVALIDATIONS : 0
HASH_VALUE : 1635416785
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 1
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : 7e8adf6b1c21e493a6bdcf5a617a7ad1
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 0000000065F7A2E8
EDITION :
SQL_ID :
OBJECT_STR : e8ec445edab00042802d511305ab90fa.$BUILD$.BOOK01P\x52\0\0\0
PL/SQL procedure successfully completed.

kgllkal count 06 -- handle address: 0000000062ce3480, mode: 1 kglnaobj address:0x62ce3648: "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648: "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
--//這裡有點特殊,也是我開始遇到的問題,才想看看該物件的內容.
--//它是sql語句的FULL_HASH_VALUE.Child:0.BOOK01P,裡面的C大寫,明顯與子游標有關,裡面的0是指child_number.
SYS@book> @ sharepool/shp4 0000000062ce3480 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 0000000062CE3480 0000000062CE3480 e8ec445edab00042802d511305ab90fa 0 0 0 000000006E2982C8 00 4064 0 0 4064 4064 3280362473 0

SYS@book> @ kglob 0 3280362473
==============================
INST_ID : 1
OWNER :
NAME : e8ec445edab00042802d511305ab90fa
DB_LINK :
NAMESPACE : SQL AREA STATS
TYPE : CURSOR STATS
NAMESPACE_NUM : 75
NAMESPACE_HEX : 4b
SHARABLE_MEM : 4064
LOADS : 1
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : YES
CHILD_LATCH : 23529
INVALIDATIONS : 0
HASH_VALUE : 3280362473
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2024-08-24/10:55:41
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 2
PINNED_TOTAL : 2
PROPERTY :
FULL_HASH_VALUE : f70b8d76d1df4847c17a0582c3865be9
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 0000000062CE3480
EDITION :
SQL_ID :
OBJECT_STR : e8ec445edab00042802d511305ab90fa
PL/SQL procedure successfully completed.
--//如果僅僅看name,無法猜測FULL_HASH_VALUE的如何計算的.

$ sql_idz.sh 'e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0\0' 3
sql_text = e8ec445edab00042802d511305ab90fa.Child:0.BOOK01P\x4b\0\0\0
full_hash_value(16) = F70B8D76D1DF4847C17A0582C3865BE9 or f70b8d76d1df4847c17a0582c3865be9
xxxxx_matching_signature(10) = -4505282418046510103 or 13941461655663041513
hash_value(10) = 3280362473
sql_id(32) = c2yh5hb1scqz9
sql_id(32) = c2yh5hb1scqz9
sql_id(32) = c2yh5hb1scqz9
--//full_hash_value(16) = f70b8d76d1df4847c17a0582c3865be9 能對上.

kgllkal count 09 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"

SYS@book> @ sharepool/shp4 000000006b97c9f8 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 000000006B97C9F8 000000006B97C9F8 SYS.1073777561 0 0 0 00 00 0 0 0 0 0 471669191 0

SYS@book> @ kglob 0 471669191
==============================
INST_ID : 1
OWNER : SYS
NAME : 1073777561
DB_LINK :
NAMESPACE : DBINSTANCE
TYPE : CURSOR
NAMESPACE_NUM : 74
NAMESPACE_HEX : 4a
SHARABLE_MEM : 0
LOADS : 0
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 72135
INVALIDATIONS : 0
HASH_VALUE : 471669191
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 1126873
PINNED_TOTAL : 0
PROPERTY :
FULL_HASH_VALUE : 25d8c2e2a1f8d5e74176b9b61c1d19c7
CON_ID : 1
CON_NAME : CDB$ROOT
ADDR : 000000006B97C9F8
EDITION :
SQL_ID :
OBJECT_STR : 1073777561.SYS.CDB$ROOT\x4a\0\0\0
PL/SQL procedure successfully completed.
--//這裡使用數字表示PDB NAME.ORACLE很奇怪,本來應該使用BOOK01P.

SYS@book> select PDB_ID,PDB_NAME,DBID,CON_UID from dba_pdbs;

PDB_ID PDB_NAME DBID CON_UID
---------- ------------------------------ ---------- ----------
3 BOOK01P 1073777561 1073777561
2 PDB$SEED 2763294012 2763294012
--//1073777561可以對上.

kgllkal count 11 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80: "5358706841214419813BOOK01P"
kglpnal count 04 -- handle address: 000000006cd389b8, mode: 2 kglnaobj address:0x6cd38b80: "5358706841214419813BOOK01P"
kgllkal count 12 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kglpnal count 05 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kgllkal count 13 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8: "13547376130454050250BOOK01P"
kglpnal count 06 -- handle address: 0000000063e7ea10, mode: 2 kglnaobj address:0x63e7ebd8: "13547376130454050250BOOK01P"
kgllkal count 14 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50: "4448762010415191240BOOK01P"
kglpnal count 07 -- handle address: 0000000066a38988, mode: 2 kglnaobj address:0x66a38b50: "4448762010415191240BOOK01P"
kgllkal count 15 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
kglpnal count 08 -- handle address: 0000000062d77660, mode: 2 kglnaobj address:0x62d77828: "1256087081022357994BOOK01P"
--//再次出現一大堆數字.

SCOTT@book01p> @o2 dept
owner object_name object_type SEG_PART_NAME status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ -------------------- -------------------- --------- ---------- ---------- ------------------- -------------------
SCOTT DEPT TABLE VALID 76191 76191 2024-08-16 09:33:38 2024-08-16 09:33:38

SCOTT@book01p> set numw 20
SCOTT@book01p> select * from sys.exp_head$ where objn=76191;
EXP_ID OBJN SUB_ID FIXED_COST TEXT COL_ FLAGS CTIME
-------------------- -------------------- -------------------- -------------------- ------------------------------ ---- -------------------- -------------------
1256087081022357994 76191 0 5.51081878534547E-08 "DEPTNO" 1 8 2024-08-16 09:35:07
13547376130454050250 76191 0 5.51081878534547E-08 "DNAME" 2 8 2024-08-16 09:35:07
4448762010415191240 76191 0 5.51081878534547E-08 "LOC" 3 8 2024-08-16 09:35:07
--//只有5358706841214419813BOOK01P沒有。
--//1256087081022357994BOOK01P 出現4次,估計與where deptno=20有關.
--//感覺這樣如果第一次執行假設表欄位很多的情況下,執行select * 豈不是要建立大量的chunk.
--//看一些介紹與_column_tracking_level隱含引數有關,設定17可以減少這類的chunk.21c等於53.
SYS@book> @ hide _column_tracking_level
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- --------------------- ---------------------- ---------------------- ---------------------- ----- ---------
_column_tracking_level column usage tracking TRUE 53 53 TRUE IMMEDIATE

SYS@book> @ 10tox 53 2
BASE10 BASE2
------- -------
53 110101
--//FULL_HASH_VALUE就不計算了.

--//看看:5358706841214419813BOOK01P
SYS@book> @ sharepool/shp4 000000006cd389b8 0
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 000000006CD389B8 000000006CD389B8 5358706841214419813 0 0 0 00 00 0 0 0 0 0 3095809875 0

SYS@book> @ kglob 0 3095809875
==============================
INST_ID : 1
OWNER :
NAME : 5358706841214419813
DB_LINK :
NAMESPACE : OPTIMIZER DIRECTIVE OWNER
TYPE : CURSOR
NAMESPACE_NUM : 104
NAMESPACE_HEX : 68
SHARABLE_MEM : 0
LOADS : 1
EXECUTIONS : 0
LOCKS : 0
PINS : 0
KEPT : NO
CHILD_LATCH : 20307
INVALIDATIONS : 0
HASH_VALUE : 3095809875
LOCK_MODE : NONE
PIN_MODE : NONE
STATUS : UNKOWN
TIMESTAMP :
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 2
PINNED_TOTAL : 2
PROPERTY :
FULL_HASH_VALUE : d1faee06f59008e2ce16faeeb8864f53
CON_ID : 3
CON_NAME : BOOK01P
ADDR : 000000006CD389B8
EDITION :
SQL_ID :
OBJECT_STR : 5358706841214419813
PL/SQL procedure successfully completed.
--//不熟悉這部分內容,放棄.

$ sql_idz.sh '5358706841214419813.BOOK01P\x68\0\0\0' 3
sql_text = 5358706841214419813.BOOK01P\x68\0\0\0
full_hash_value(16) = D1FAEE06F59008E2CE16FAEEB8864F53 or d1faee06f59008e2ce16faeeb8864f53
xxxxx_matching_signature(10) = -3596411349203792045 or 14850332724505759571
hash_value(10) = 3095809875
sql_id(32) = cw5ruxuw8cmum
sql_id(32) = cw5ruxuw8cmum
sql_id(32) = cw5ruxuw8cmum

6.簡單總結:

--//如果看vage的書<oracle核心技術解密>,這樣簡單的sql語句,第1次硬解析執行共10次呼叫,其中kgllkal 7次,kglpnal 3次.
--//而21c呼叫明顯增加,第1次硬解析執行共15+8=23次呼叫,其中kgllkal 15次,kglpnal 8次.
--//如果按照後面的測試還多3次.
--//再次提醒OLTP系統使用繫結變數的重要性,應該再加兩個字"合理"地使用.

--//看看sql執行鎖模式的情況.1表示NULL 2表示共享 3表示排他.第1次執行出現mode=3的情況2次.
kglpnal count 01 -- handle address: 000000006c880250, mode: 3 kglnaobj address:0x6c880418: ""
kglpnal count 02 -- handle address: 0000000062ce3480, mode: 3 kglnaobj address:0x62ce3648: "e8ec445edab00042802d511305ab90faChild:0BOOK01P"
--//僅僅出現2次mode: 3,而且是kglpnal,這樣表不存在大量密集執行,應該看到library cache pin.不應該是library cache lock.
--//也許作者和我取的值$rcx不對,該問題暫時放一放,

--//最後,也許我的探究存在許多缺陷,許多都是受限自己的能力亂猜,不知道是否正確.

--//在結束測試時,我有換1條sql語句嘗試:
kgllkal count 93 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298: "Select * from dept where deptno=10"
kglpnal count 35 -- handle address: 0000000065b0d0d0, mode: 2 kglnaobj address:0x65b0d298: "Select * from dept where deptno=10"
kgllkal count 94 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300: "bookSYSCDB$ROOT"
kgllkal count 95 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"
--//不知道為什麼我前面的嘗試少了前面這3行.也許重新整理共享池時這些物件時沒有清楚乾淨,再測試前我執行了1次,再重新整理共享池的.
kgllkal count 96 -- handle address: 000000006e261978, mode: 2 kglnaobj address:0x6e261b40: "85d6f5c0bce7df033db8e86ed0624d44$BUILD$BOOK01P"
kgllkal count 97 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70: ""
kglpnal count 36 -- handle address: 000000006f9af9a8, mode: 3 kglnaobj address:0x6f9afb70: ""
kgllkal count 98 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300: "bookSYSCDB$ROOT"
kgllkal count 99 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"
kgllkal count 100 -- handle address: 0000000063d4de10, mode: 1 kglnaobj address:0x63d4dfd8: "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kglpnal count 37 -- handle address: 0000000063d4de10, mode: 3 kglnaobj address:0x63d4dfd8: "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kgllkal count 101 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78: "SCOTTBOOK01P"
kgllkal count 102 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300: "bookSYSCDB$ROOT"
kgllkal count 103 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0: "1073777561SYSCDB$ROOT"
kgllkal count 104 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 38 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 105 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320: "5358706841214419813BOOK01P"
kglpnal count 39 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320: "5358706841214419813BOOK01P"
kgllkal count 106 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0: "1256087081022357994BOOK01P"
kglpnal count 40 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0: "1256087081022357994BOOK01P"
kgllkal count 107 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8: "13547376130454050250BOOK01P"
kglpnal count 41 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8: "13547376130454050250BOOK01P"
kgllkal count 108 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138: "4448762010415191240BOOK01P"
kglpnal count 42 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138: "4448762010415191240BOOK01P"
kgllkal count 109 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0: "1256087081022357994BOOK01P"
kglpnal count 43 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0: "1256087081022357994BOOK01P"
--//這次多了3個呼叫.

kgllkal count 110 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298: "Select * from dept where deptno=10"
kgllkal count 111 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70: ""
kgllkal count 112 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 44 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8: "DEPTSCOTTBOOK01Pp~\027k"

kgllkal count 113 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298: "Select * from dept where deptno=10"
kgllkal count 114 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70: ""
--//感覺出現這樣的機會更大一些.

7.附上執行指令碼的原始碼:

$ cat kglob.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: kglob.sql
-- Purpose: query v$db_object_cache view
--
-- Author: lfree
--
-- Usage:
-- @ kglob <sql_id> <hash_value>
-- for example
-- @ kglob 0 123456678
-- @ kglob 7h35uxf5uhmm1 0
--
--------------------------------------------------------------------------------
set term off head off
define noprint='noprint'

col tpt_version_old &noprint new_value _tpt_version_old
col tpt_version_new &noprint new_value _tpt_version_new
col tpt_noprint &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old
,CASE WHEN v > 10 THEN '' ELSE '--' END tpt_version_new
FROM version;

select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
inst_id INST_ID
,KGLNAOWN OWNER
,kglnaobj NAME
,kglnadlk DB_LINK
,kglhdnsd NAMESPACE
,kglobtyd TYPE
,kglhdnsp NAMESPACE_NUM
,to_char(kglhdnsp,'FMxx') NAMESPACE_HEX
,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6 SHARABLE_MEM
,kglhdldc LOADS
,kglhdexc EXECUTIONS
,kglhdlkc LOCKS
,kglobpc0 PINS
,decode(kglhdkmk,0,'NO','YES') KEPT
,kglhdclt CHILD_LATCH
,kglhdivc INVALIDATIONS
,kglnahsh HASH_VALUE
,decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN') LOCK_MODE
,decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN') PIN_MODE
,decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN') STATUS
,substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19) TIMESTAMP
,substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19) PREVIOUS_TIMESTAMP
,kgloblct LOCKED_TOTAL
,kglobpct PINNED_TOTAL
,kglobprop PROPERTY
,kglnahsv FULL_HASH_VALUE
&&_tpt_version_new ,con_id CON_ID
&&_tpt_version_new ,KGLNACON CON_NAME
,kglhdadr ADDR
,kglnaedn EDITION
,KGLOBT03 SQL_ID
&&_tpt_version_old,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60) end OBJECT_STR
&&_tpt_version_new,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||kglnacon||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60) OBJECT_STR
from x$kglob
where kglnaobj is not null and (KGLOBT03 = lower('&1') or KGLNAHSH= &2);
set term on head on
@ pr
--//注:pr來自tpt包.

$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
column fcura_addrlen new_value _fcura_addrlen format 999
column handle_type format a22

set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;
set termout on

SELECT DECODE (kglhdadr,
kglhdpar, 'parent handle address',
'child handle address')
handle_type,
kglhdadr,
kglhdpar,
--//substr(kglnaobj,1,40) c40,
substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40) c40,
KGLHDLMD,
KGLHDPMD,
kglhdivc,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE
KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0')
or KGLOBT03 = lower('&1')
or KGLNAHSH= &2;

--//注:裡面^M在vim下ctrl+v,ctrl+M輸入.
$ cat -v $(which sql_idz.sh )
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
# argv1 sql statement or sql of text file
# argv2 flag: 0= sql statement 1=sql of text file for sqlplus 2=sql of text file for other 3=original
# argv3 default = '\0' add tailstr

odebug=${ODEBUG:-0}
oflag=${2:-0}
tailstr=${3:-'\0'}

if [ $oflag -eq 0 ]
then
sql_text=${1}${tailstr}
fi

# sqlplus format sql_text
if [ $oflag -eq 1 ]
then
sql_text="$( cat $1 | sed -e "s/^M$//" -e "s/\s*$//" -e '$s/;$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*$//')"'\0'
fi

# other format sql_text
if [ $oflag -eq 2 ]
then
sql_text="$( cat $1 | sed '$s/;\s*$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*//')"'\0'
fi

# exact_matching_signature, force_matching_signature
if [ $oflag -eq 3 ]
then
sql_text=${1}
fi

v1=$(echo -e -n "$sql_text" | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z')
v2=${v1:(-16):16}
v3=${v2:(-8):8}
# v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n')
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n')

if [ $odebug -eq 1 ] ; then
echo v1=$v1 v2=$v2 v3=$v3
fi

echo "sql_text = $sql_text"
echo "full_hash_value(16) = $v1 or ${v1,,}"

if [ $oflag -eq 3 ] ; then
echo "xxxxx_matching_signature(10) = $(( 16#$v2 )) or " $(echo $(( 16#$v2 )) + 2^64|bc )
fi

echo "hash_value(10) = $(( 16#$v3 )) "

BASE32=($(echo {0..9} {a..z} | tr -d 'eilo'))
res=''
for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n')
do
res=${res}${BASE32[$(( 10#$i ))]}
done
echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')"
echo "sql_id(32) = $(printf "%013s" $res)"

res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' ))
echo "sql_id(32) = $(printf "%013s" $res1)"

$ cat nmsp.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name: nmsp.sql
-- Purpose: query namespace from x$kglst
--
-- Author: lfree
--
-- Usage:
-- @ nmsp <namespace> <namespace_num>
-- for example
-- @ nmsp %table% -1
-- @ name '' 74
--
--------------------------------------------------------------------------------
column nmsp_hex format a8
select kglstdsc,kglstidn,to_char(kglstidn,'FMxx') nmsp_hex from x$kglst where KGLSTTYP='NAMESPACE' and (kglstdsc like upper('&1') or kglstidn = &2) order by 2