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

来源：这里教程网时间：2026-03-03 20:34:17 作者：

[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: "" --//感觉出现这样的机会更大一些. --// ==10实际上一个等号 no = 10 是违规字符. 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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推