[20241006]跟踪library cache lock library cache pin使用gdb(补充测试3).txt

[20241006]跟踪library cache lock library cache pin使用gdb(补充测试3).txt --//补充测试产生光标已经缓存的情况下，生成新子光标的调用library cache lock library cache pin的情况。 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. 2.测试： --//首先执行如下命令多次。 @ desc dept Select * from dept where deptno=20; --//session 1： SCOTT@book01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                               PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------        280      22249 3606                     DEDICATED 3608                                90          7 alter system kill session '280,22249' immediate; --//window 1: $ rlgdb -f -p 3608  -x /home/oracle/sqllaji/gdb/lkpn.gdb ... 0x00007f92e6ec6480 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:0x7f92e6ec6480 Breakpoint 1 at 0x15367e90 Breakpoint 2 at 0x1536c020 --//session 1： select * from dept where deptno=20; select * from dept where deptno=20; select * from dept where deptno=20; select * from dept where deptno=20; --//window 1: (gdb) c Continuing. --//第1次执行： kgllkal count 01 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kglpnal count 01 -- handle address: 0000000065602ca0, mode: 2 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 02 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 03 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" kgllkal count 04 -- handle address: 0000000065625b10, mode: 2 kglnaobj address:0x65625cd8:      "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P" kgllkal count 05 -- handle address: 0000000065f7ead0, mode: 1 kglnaobj address:0x65f7ec98:      "" kglpnal count 02 -- handle address: 0000000065f7ead0, mode: 3 kglnaobj address:0x65f7ec98:      "" kgllkal count 06 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 07 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" kgllkal count 08 -- handle address: 0000000065c776d8, mode: 1 kglnaobj address:0x65c778a0:      "e8ec445edab00042802d511305ab90faChild:0BOOK01P" kglpnal count 03 -- handle address: 0000000065c776d8, mode: 3 kglnaobj address:0x65c778a0:      "e8ec445edab00042802d511305ab90faChild:0BOOK01P" kgllkal count 09 -- handle address: 00000000629993c0, mode: 1 kglnaobj address:0x62999588:      "SCOTTBOOK01P" kgllkal count 10 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 11 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" kgllkal count 12 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01PЫ\231b" kglpnal count 04 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01PЫ\231b" kgllkal count 13 -- handle address: 00000000657870b0, mode: 2 kglnaobj address:0x65787278:      "5358706841214419813BOOK01P" kglpnal count 05 -- handle address: 00000000657870b0, mode: 2 kglnaobj address:0x65787278:      "5358706841214419813BOOK01P" kgllkal count 14 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kglpnal count 06 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kgllkal count 15 -- handle address: 0000000065742110, mode: 2 kglnaobj address:0x657422d8:      "13547376130454050250BOOK01P" kglpnal count 07 -- handle address: 0000000065742110, mode: 2 kglnaobj address:0x657422d8:      "13547376130454050250BOOK01P" kgllkal count 16 -- handle address: 000000006b39bfe0, mode: 2 kglnaobj address:0x6b39c1a8:      "4448762010415191240BOOK01P" kglpnal count 08 -- handle address: 000000006b39bfe0, mode: 2 kglnaobj address:0x6b39c1a8:      "4448762010415191240BOOK01P" kgllkal count 17 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kglpnal count 09 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" --//第2次执行： kgllkal count 18 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 19 -- handle address: 0000000065f7ead0, mode: 1 kglnaobj address:0x65f7ec98:      "" kgllkal count 20 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01PЫ\231b" kglpnal count 10 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01PЫ\231b" --//第3次执行： kgllkal count 21 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 22 -- handle address: 0000000065f7ead0, mode: 1 kglnaobj address:0x65f7ec98:      "" --//第4次执行： --//没有任何输出。 --//session 1： SCOTT@book01p> alter session set optimizer_index_cost_adj=99; Session altered. select * from dept where deptno=20; select * from dept where deptno=20; select * from dept where deptno=20; select * from dept where deptno=20; --//window 1: kgllkal count 23 -- handle address: 0000000069e5e158, mode: 1 kglnaobj address:0x69e5e320:      "alter session set optimizer_index_cost_adj=99" kglpnal count 11 -- handle address: 0000000069e5e158, mode: 2 kglnaobj address:0x69e5e320:      "alter session set optimizer_index_cost_adj=99" kgllkal count 24 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 25 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" --//第1次执行： kgllkal count 26 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 27 -- handle address: 0000000065f7ead0, mode: 1 kglnaobj address:0x65f7ec98:      "" kgllkal count 28 -- handle address: 0000000065625b10, mode: 2 kglnaobj address:0x65625cd8:      "e8ec445edab00042802d511305ab90fa$BUILD$BOOK01P" --//一样需要build1次。 kgllkal count 29 -- handle address: 000000006d5462b0, mode: 1 kglnaobj address:0x6d546478:      "" kglpnal count 12 -- handle address: 000000006d5462b0, mode: 3 kglnaobj address:0x6d546478:      "" kgllkal count 30 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 31 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" kgllkal count 32 -- handle address: 000000006e0a0e20, mode: 1 kglnaobj address:0x6e0a0fe8:      "e8ec445edab00042802d511305ab90faChild:1BOOK01P" kglpnal count 13 -- handle address: 000000006e0a0e20, mode: 3 kglnaobj address:0x6e0a0fe8:      "e8ec445edab00042802d511305ab90faChild:1BOOK01P" kgllkal count 33 -- handle address: 00000000629993c0, mode: 1 kglnaobj address:0x62999588:      "SCOTTBOOK01P" kgllkal count 34 -- handle address: 0000000070250ab0, mode: 2 kglnaobj address:0x70250c78:      "bookSYSCDB$ROOT" kgllkal count 35 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT" kgllkal count 36 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01P`\224\231b" kglpnal count 14 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01P`\224\231b" kgllkal count 37 -- handle address: 000000006efcad68, mode: 1 kglnaobj address:0x6efcaf30:      "select user#,password,datats#,tempts#,type#,defrole,resource$,ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2,nvl(spare3,16382),spare9,spare10 fro"... kgllkal count 38 -- handle address: 0000000065c53648, mode: 1 kglnaobj address:0x65c53810:      "" kgllkal count 39 -- handle address: 000000006565f1a0, mode: 2 kglnaobj address:0x6565f368:      "2f24fd3a57be7c66506fb66ca24fe83c$BUILD$BOOK01P" kgllkal count 40 -- handle address: 000000006efca608, mode: 1 kglnaobj address:0x6efca7d0:      "" kglpnal count 15 -- handle address: 000000006efca608, mode: 3 kglnaobj address:0x6efca7d0:      "" kgllkal count 41 -- handle address: 000000006bfb0e10, mode: 1 kglnaobj address:0x6bfb0fd8:      "2f24fd3a57be7c66506fb66ca24fe83cChild:0BOOK01P" kglpnal count 16 -- handle address: 000000006bfb0e10, mode: 3 kglnaobj address:0x6bfb0fd8:      "2f24fd3a57be7c66506fb66ca24fe83cChild:0BOOK01P" kgllkal count 42 -- handle address: 000000006b834f40, mode: 2 kglnaobj address:0x6b835108:      "USER$SYSBOOK01P" kglpnal count 17 -- handle address: 000000006b834f40, mode: 2 kglnaobj address:0x6b835108:      "USER$SYSBOOK01P" kgllkal count 43 -- handle address: 000000006b856170, mode: 2 kglnaobj address:0x6b856338:      "C_USER#SYSBOOK01P" kglpnal count 18 -- handle address: 000000006b856170, mode: 2 kglnaobj address:0x6b856338:      "C_USER#SYSBOOK01P" kgllkal count 44 -- handle address: 000000006efca608, mode: 1 kglnaobj address:0x6efca7d0:      "" kgllkal count 45 -- handle address: 000000006b834f40, mode: 2 kglnaobj address:0x6b835108:      "USER$SYSBOOK01P" kglpnal count 19 -- handle address: 000000006b834f40, mode: 2 kglnaobj address:0x6b835108:      "USER$SYSBOOK01P" kgllkal count 46 -- handle address: 000000006b856170, mode: 2 kglnaobj address:0x6b856338:      "C_USER#SYSBOOK01P" kglpnal count 20 -- handle address: 000000006b856170, mode: 2 kglnaobj address:0x6b856338:      "C_USER#SYSBOOK01P" kgllkal count 47 -- handle address: 00000000657870b0, mode: 2 kglnaobj address:0x65787278:      "5358706841214419813BOOK01P" kglpnal count 21 -- handle address: 00000000657870b0, mode: 2 kglnaobj address:0x65787278:      "5358706841214419813BOOK01P" kgllkal count 48 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kglpnal count 22 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kgllkal count 49 -- handle address: 0000000065742110, mode: 2 kglnaobj address:0x657422d8:      "13547376130454050250BOOK01P" kglpnal count 23 -- handle address: 0000000065742110, mode: 2 kglnaobj address:0x657422d8:      "13547376130454050250BOOK01P" kgllkal count 50 -- handle address: 000000006b39bfe0, mode: 2 kglnaobj address:0x6b39c1a8:      "4448762010415191240BOOK01P" kglpnal count 24 -- handle address: 000000006b39bfe0, mode: 2 kglnaobj address:0x6b39c1a8:      "4448762010415191240BOOK01P" kgllkal count 51 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" kglpnal count 25 -- handle address: 00000000656659c0, mode: 2 kglnaobj address:0x65665b88:      "1256087081022357994BOOK01P" --//可以发现生成子光标非常类似硬解析。 --//第2次执行： kgllkal count 52 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 53 -- handle address: 000000006d5462b0, mode: 1 kglnaobj address:0x6d546478:      "" kgllkal count 54 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01P`\224\231b" kglpnal count 26 -- handle address: 000000006288ca30, mode: 2 kglnaobj address:0x6288cbf8:      "DEPTSCOTTBOOK01P`\224\231b" --//第3次执行： kgllkal count 55 -- handle address: 0000000065602ca0, mode: 1 kglnaobj address:0x65602e68:      "select * from dept where deptno=20" kgllkal count 56 -- handle address: 000000006d5462b0, mode: 1 kglnaobj address:0x6d546478:      "" --//第4次执行： --//没有任何输出。 --//session 2: SYS@book> @ sharepool/shp4 80baj2c2ur47u 0 HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 ---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address   0000000065F7EAD0 0000000065602CA0 select * from dept where deptno=20                1          0          0 0000000065743A50 0000000061755668       8128      20224       5203     33555      33555   95129850 80baj2c2ur47u          0 child handle address   000000006D5462B0 0000000065602CA0 select * from dept where deptno=20                1          0          0 000000006BFA9A40 000000006E1FE250       8128      20224       5203     33555      33555   95129850 80baj2c2ur47u          1 parent handle address  0000000065602CA0 0000000065602CA0 select * from dept where deptno=20                1          0          0 0000000065C78948 00                     8128          0          0      8128       8128   95129850 80baj2c2ur47u      65535 3.附上lkpn.gdb代码： $ cat gdb/lkpn.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 $rcx==3 #break kgllkal if ( $rcx==3 && $rdx==0x00000000670C9E58 ) #break kgllkal if $rdx==0x00000000670C9E58 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