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

[20241105]跟踪library cache lock library cache pin使用gdb(11g)2.txt --//前一段时间写的使用gdb跟踪library cache lock/library cache pin的脚本。 --//我看过以前的笔记，当时测试过链接https://nenadnoveljic.com/blog/library-cache-lock-debugger/,我的测试在11g是失败. --//今天有空再次尝试,确实不成功,不过有了前面测试的经验,可以很容易写成11g的gdb监测脚本. 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试gdb脚本： --//我直接给出gdb脚本,一些细节忽略,主要讲解比较麻烦,就是在gdb下设置kgllkal,kglpnal断点.然后使用info register查看寄存器内 --//容,然后获取该执行语句的sql_id,hash_value值,继续退出,然后执行: @ sharepool/shp4 <sql_id> 0 --//然后查看KGLHDADR的值,对比就知道那个寄存器保存的是对象句柄地址,测试结果对应的寄存器是rsi,至于mode我仅仅猜测是寄存器 --//rdx.这样稍微改改11g的监测脚本就可以写成. --//里面的偏移量很容易确定,直接opeek 地址 长度 1,看看偏移地址在那里就可以了,11g我的测试结果在0x1a8 = 424. $ cat lkpn11g.gdb set pagination off #set print repeats 0 #set print elements 0 set logging file /tmp/lkpn.log set logging overwrite on set logging on set $lk  = 0 set $pn  = 0 set $lock  = 0 #break kgllkal if $rdx==3 #break kgllkal if ( $rdx==3 && $rsi==0x00000000670C9E58 ) #break kgllkal if $rsi==0x00000000670C9E58 break kgllkal commands  silent  printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rsi ,$rdx  echo kglnaobj address:  x/s $rsi+0x1a8  c  end #break kglpnal if $rcx==3 break kglpnal commands  silent  printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rsi ,$rdx  echo kglnaobj address:  x/s $rsi+0x1a8  c  end 3.测试： --//session 1: SCOTT@book> @ spid ============================== SID                           : 18 SERIAL#                       : 44459 PROCESS                       : 65221 SERVER                        : DEDICATED SPID                          : 65222 PID                           : 25 P_SERIAL#                     : 96 KILL_COMMAND                  : alter system kill session '18,44459' immediate; PL/SQL procedure successfully completed. --//执行desc dept以及Select * from dept where deptno=20;(开头S大小)多次避免递归调用。 --//window 1: $ rlgdb -f -p  65222 -x lkpn11g.gdb .. 0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0 Breakpoint 1 at 0x983da94 Breakpoint 2 at 0x9839f5c --//session 1: SCOTT@book> select * from dept where deptno= 12; no rows selected --//window 1: (gdb) c Continuing. kgllkal count 01 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8:       "select * from dept where deptno= 12" kglpnal count 01 -- handle address: 000000007bcb6f50, mode: 2 kglnaobj address:0x7bcb70f8:       "select * from dept where deptno= 12" kgllkal count 02 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 03 -- handle address: 000000007c19ee50, mode: 2 kglnaobj address:0x7c19eff8:       "2710e6f2 1d05c1f$BUILD$" --//怎么中间出现1个空格。~~~~~~~~~~~~~ kgllkal count 04 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8:       "" kglpnal count 02 -- handle address: 000000007cb51930, mode: 3 kglnaobj address:0x7cb51ad8:       "" kgllkal count 05 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 06 -- handle address: 000000007bcec280, mode: 1 kglnaobj address:0x7bcec428:       "fa4ab910ef98d2aa2710e6f21d05c1fChild:0" kglpnal count 03 -- handle address: 000000007bcec280, mode: 3 kglnaobj address:0x7bcec428:       "fa4ab910ef98d2aa2710e6f21d05c1fChild:0" kgllkal count 07 -- handle address: 000000007bcfc0a0, mode: 1 kglnaobj address:0x7bcfc248:       "SCOTT" kgllkal count 08 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 09 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" kglpnal count 04 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" kgllkal count 10 -- handle address: 000000007da13e58, mode: 1 kglnaobj address:0x7da14000:       "\220\021" kgllkal count 11 -- handle address: 000000007ef0a260, mode: 2 kglnaobj address:0x7ef0a408:       "ICOL$SYS\bz骪177" kglpnal count 05 -- handle address: 000000007ef0a260, mode: 2 kglnaobj address:0x7ef0a408:       "ICOL$SYS\bz骪177" --//以上第1次执行,有2个空是子游标句柄,对象内容在父游标句柄里面,有时候取到乱码也是正常的. kgllkal count 12 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8:       "select * from dept where deptno =12" kgllkal count 13 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8:       "" kgllkal count 14 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" kglpnal count 06 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" --//以上第2次执行 kgllkal count 15 -- handle address: 000000007bcb6f50, mode: 1 kglnaobj address:0x7bcb70f8:       "select * from dept where deptno =12" kgllkal count 16 -- handle address: 000000007cb51930, mode: 1 kglnaobj address:0x7cb51ad8:       "" --//以上第3次执行 --//以上第4次执行,没有任何输出,光标已经cache. SYS@book> @ sharepool/shp4 2f476y80x0r0z 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   000000007CB51930 000000007BCB6F50 select * from dept where deptno =12                0          0          0 000000007CA23A58 000000007C8693C0       4528      12144       3067     19739      19739   30432287 2f476y80x0r0z          0 parent handle address  000000007BCB6F50 000000007BCB6F50 select * from dept where deptno =12                0          0          0 000000007CB39AD0 00                     4720          0          0      4720       4720   30432287 2f476y80x0r0z      65535 --//下划线的对象有一点点奇怪中间有空格. SYS@book> @ sharepool/shp4z 000000007c19ee50 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- parent handle address  000000007C19EE50 000000007C19EE50 $BUILD$.2710e6f2 1d05c1f                          0          0          0 00               00                        0          0          0         0          0  438406682                        0 --//hash_value=438406682 SYS@book> select * from v$db_object_cache where hash_value=438406682   2   @pr ============================== OWNER                         : $BUILD$ NAME                          : 2710e6f2 1d05c1f DB_LINK                       : NAMESPACE                     : SQL AREA BUILD TYPE                          : CURSOR SHARABLE_MEM                  : 0 LOADS                         : 0 EXECUTIONS                    : 0 LOCKS                         : 0 PINS                          : 0 KEPT                          : NO CHILD_LATCH                   : 101914 INVALIDATIONS                 : 0 HASH_VALUE                    : 438406682 LOCK_MODE                     : NONE PIN_MODE                      : NONE STATUS                        : UNKOWN TIMESTAMP                     : PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 1 PINNED_TOTAL                  : 0 PROPERTY                      : FULL_HASH_VALUE               : 6837047da7fa359a5549f81b1a218e1a PL/SQL procedure successfully completed. --//才发现11g下v$db_object_cache视图显示的字段太少。 SYS@book> select name,dump(name ,16) c70 from v$db_object_cache where hash_value=438406682; NAME                                     C70 ---------------------------------------- ---------------------------------------------------------------------- 2710e6f2 1d05c1f                         Typ=1 Len=16: 32,37,31,30,65,36,66,32,20,31,64,30,35,63,31,66 --//确实有1个空格.0x20. $ sql_idz.sh "select * from dept where deptno= 12\0" 3 sql_text = select * from dept where deptno= 12\0 full_hash_value(16) = FA4AB910EF98D2AA2710E6F201D05C1F xxxxx_matching_signature(10) = 2815003694193466399 or  21261747767903018015 hash_value(10) = 30432287 or hash_value(16) = 01D05C1F sql_id(32) = 2f476y80x0r0z sql_id(32) = 2f476y80x0r0z sql_id(32) = 2f476y80x0r0z --//对象2710e6f2 1d05c1f$BUILD$的来源是sql语句的FULL_HASH_VALUE的16进制的后16位2710E6F201D05C1F,分开2部分是 --//2710E6F2,01D05C1F,oracle设计有点奇葩,01D05C1F前面的0变成空格吗?看看FULL_HASH_VALUE计算是否正确. --//kgllkal count 06 -- handle address: 000000007bcec280, mode: 1 kglnaobj address:0x7bcec428:       "fa4ab910ef98d2aa2710e6f21d05c1fChild:0" --//full_hash_value(16) = FA4AB910EF98D2AA2710E6F201D05C1F fa4ab910ef98d2aa2710e6f21d05c1f fa4ab910ef98d2aa2710e6f201d05c1f --//对象fa4ab910ef98d2aa2710e6f21d05c1fChild:0 也是丢失0. SYS@book> @ nmsp 'SQL AREA BUILD' -1 @ nmsp table -1 @ nmsp '' 74  or @ nmsp '' 0x4a|x4a KGLSTDSC                                                           KGLSTIDN KGLSTIDN_HEX ---------------------------------------------------------------- ---------- ------------ SQL AREA BUILD                                                           82 52 --//命名空间82，16进制0x52. $ sql_idz.sh '2710e6f2 1d05c1f.$BUILD$\x52\0\0\0' 3 sql_text = 2710e6f2 1d05c1f.$BUILD$\x52\0\0\0 full_hash_value(16) = 6837047DA7FA359A5549F81B1A218E1A xxxxx_matching_signature(10) = 6145715961809964570 or  24592460035519516186 hash_value(10) = 438406682 or hash_value(16) = 1A218E1A sql_id(32) = 5akgs3cd233hu sql_id(32) = 5akgs3cd233hu sql_id(32) = 5akgs3cd233hu --//确实使用空格计算的.full_hash_value(16) = 6837047DA7FA359A5549F81B1A218E1A与前面的使用如下语句的查询结果一致。 --//select * from v$db_object_cache where hash_value=438406682 kgllkal count 73 -- handle address: 000000007be33760, mode: 1 kglnaobj address:0x7be33908:       "select * from dept where deptno =15" kglpnal count 29 -- handle address: 000000007be33760, mode: 2 kglnaobj address:0x7be33908:       "select * from dept where deptno =15" kgllkal count 74 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 75 -- handle address: 000000007cab2918, mode: 2 kglnaobj address:0x7cab2ac0:       " c3ba9c9ee26d7a6$BUILD$"   --//空格再次出现。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ kgllkal count 76 -- handle address: 000000007db6e0d0, mode: 1 kglnaobj address:0x7db6e278:       "\220\021" -//乱码 kglpnal count 30 -- handle address: 000000007db6e0d0, mode: 3 kglnaobj address:0x7db6e278:       "\220\021" kgllkal count 77 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 78 -- handle address: 000000007c9cd2c8, mode: 1 kglnaobj address:0x7c9cd470:       "f2afd967b55d9efac3ba9c9ee26d7a6Child:0" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ kglpnal count 31 -- handle address: 000000007c9cd2c8, mode: 3 kglnaobj address:0x7c9cd470:       "f2afd967b55d9efac3ba9c9ee26d7a6Child:0" kgllkal count 79 -- handle address: 000000007bcfc0a0, mode: 1 kglnaobj address:0x7bcfc248:       "SCOTT" kgllkal count 80 -- handle address: 000000007e3371e0, mode: 2 kglnaobj address:0x7e337388:       "bookSYS" kgllkal count 81 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" kglpnal count 32 -- handle address: 000000007d2defd0, mode: 2 kglnaobj address:0x7d2df178:       "DEPTSCOTT" $ sql_idz.sh "select * from dept where deptno= 15\0" 3 sql_text = select * from dept where deptno= 15\0 full_hash_value(16) = F2AFD967B55D9EFA0C3BA9C9EE26D7A6 xxxxx_matching_signature(10) = 881484836830107558 or  19328228910539659174 hash_value(10) = 3995522982 or hash_value(16) = EE26D7A6 sql_id(32) = 0sfx9t7r2dpx6 sql_id(32) =  sfx9t7r2dpx6 sql_id(32) =  sfx9t7r2dpx6 --//视乎11g下有一些不能理解的地方,开头的0变成空格. --//f2afd967b55d9efac3ba9c9ee26d7a6 长度31也去掉0. --//F2AFD967B55D9EFA0C3BA9C9EE26D7A6 长度32. --//这里的0是字符'0',不是chr(0),oracle 11g为什么要这样设计变成空格呢？不理解。