[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为什么要这样设计变成空格呢？不理解。