[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

posted @   lfree  阅读(7)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示