[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