[20240825]记录表不存在sql语句执行调用kgllkal,kglpnal的情况(21c).txt

[20240825]记录表不存在sql语句执行调用kgllkal,kglpnal的情况(21c).txt

--//记录表不存在sql语句执行调用kgllkal,kglpnal的情况(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.

2. 检测gdb脚本:
$ 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

3.测试:
--//session 1:
SCOTT@book01p> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID                               PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------------------------------ ------- ---------- --------------------------------------------------
       161      25798 3568                     DEDICATED 3570                               125          1 alter system kill session '161,25798' immediate;

Select * from deptxxx; --//执行多次,避免递归.

--//sesson 2:
$ rlgdb -f -p 3570 -x lkpn.gdb
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-94.el7
Copyright (C) 2013 Free Software Foundation, Inc.
....
0x00007fa159bcc480 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:0x7fa159bcc480
Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb)

--//session 1:
SCOTT@book01p> select * from deptxxx;

--//挂起!!
--//sesson 2:
--//按c继续:
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000006c9dc658, mode: 1 kglnaobj address:0x6c9dc820:      "select * from deptxxx"
kglpnal count 01 -- handle address: 000000006c9dc658, mode: 2 kglnaobj address:0x6c9dc820:      "select * from deptxxx"
kgllkal count 02 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 03 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 04 -- handle address: 000000006c9b6608, mode: 2 kglnaobj address:0x6c9b67d0:      "eb9a82687c1923350a8f6a35b8264b04$BUILD$BOOK01P"
kgllkal count 05 -- handle address: 000000006c956388, mode: 1 kglnaobj address:0x6c956550:      ""
kglpnal count 02 -- handle address: 000000006c956388, mode: 3 kglnaobj address:0x6c956550:      ""
kgllkal count 06 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 07 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 08 -- handle address: 000000006c956158, mode: 1 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kglpnal count 03 -- handle address: 000000006c956158, mode: 3 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kgllkal count 09 -- handle address: 0000000062991950, mode: 1 kglnaobj address:0x62991b18:      "SCOTTBOOK01P"
kgllkal count 10 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 11 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 12 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kglpnal count 04 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kgllkal count 13 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
kglpnal count 05 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
--//第1次执行.共调用13+5次.

kgllkal count 14 -- handle address: 000000006c9dc658, mode: 1 kglnaobj address:0x6c9dc820:      "select * from deptxxx"
kgllkal count 15 -- handle address: 000000006c9b6608, mode: 2 kglnaobj address:0x6c9b67d0:      "eb9a82687c1923350a8f6a35b8264b04$BUILD$BOOK01P"
kgllkal count 16 -- handle address: 000000006c956388, mode: 1 kglnaobj address:0x6c956550:      ""
kglpnal count 06 -- handle address: 000000006c956388, mode: 3 kglnaobj address:0x6c956550:      ""
kgllkal count 17 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 18 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 19 -- handle address: 000000006c956158, mode: 1 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kglpnal count 07 -- handle address: 000000006c956158, mode: 3 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kgllkal count 20 -- handle address: 0000000062991950, mode: 1 kglnaobj address:0x62991b18:      "SCOTTBOOK01P"
kgllkal count 21 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 22 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 23 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kglpnal count 08 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kgllkal count 24 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
kglpnal count 09 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
--//第2次执行.共调用11+4次.

kgllkal count 25 -- handle address: 000000006c9dc658, mode: 1 kglnaobj address:0x6c9dc820:      "select * from deptxxx"
kgllkal count 26 -- handle address: 000000006c9b6608, mode: 2 kglnaobj address:0x6c9b67d0:      "eb9a82687c1923350a8f6a35b8264b04$BUILD$BOOK01P"
kgllkal count 27 -- handle address: 000000006c956388, mode: 1 kglnaobj address:0x6c956550:      ""
kglpnal count 10 -- handle address: 000000006c956388, mode: 3 kglnaobj address:0x6c956550:      ""
kgllkal count 28 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 29 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 30 -- handle address: 000000006c956158, mode: 1 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kglpnal count 11 -- handle address: 000000006c956158, mode: 3 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
kgllkal count 31 -- handle address: 0000000062991950, mode: 1 kglnaobj address:0x62991b18:      "SCOTTBOOK01P"
kgllkal count 32 -- handle address: 00000000701fefe0, mode: 2 kglnaobj address:0x701ff1a8:      "bookSYSCDB$ROOT"
kgllkal count 33 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 34 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kglpnal count 12 -- handle address: 00000000655860f0, mode: 2 kglnaobj address:0x655862b8:      "DEPTXXXSCOTTBOOK01P"
kgllkal count 35 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
kglpnal count 13 -- handle address: 0000000065584690, mode: 2 kglnaobj address:0x65584858:      "DEPTXXXPUBLICBOOK01P"
--//第3次执行.共调用11+4次.第2次与3次执行调用类似,因为表不存在,相当于每次都是硬解析.
--//另外出现DEPTXXXPUBLICBOOK01P的对象,因为表deptxxx不存在,多了2次尝试public.deptxxx对象的尝试.
--//而且oracle视乎不管这些对象是否存在,先调用kgllkal,再调用kglpnal.

4.后记:
--//注意看mode=3,可以发现:
kglpnal count 10 -- handle address: 000000006c956388, mode: 3 kglnaobj address:0x6c956550:      "" --//sql语句的child handle address,取偏移没有信息正常.
kglpnal count 11 -- handle address: 000000006c956158, mode: 3 kglnaobj address:0x6c956320:      "eb9a82687c1923350a8f6a35b8264b04Child:0BOOK01P"
--//都出现在kglpnal上,可以推断如果表不存在,相关sql语句密集执行出现library chache pin等待集中在sql语句的child handle
--//address,eb9a82687c1923350a8f6a35b8264b04上.

--//为什么当表不存在,相关sql语句密集执行时会遇到library cache lock等待事件在21c,看上面调用的情况测试应该出现在library
--//ache pin上.不知道为什么,还有在这样的情况下,为什么会产生大量子光标的情况.

--//mode : 1表示NULL 2表示共享 3表示排他,另外写blog分析.
posted @   lfree  阅读(11)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2021-09-01 [20210831]bbed读取数据块6.txt
点击右上角即可分享
微信分享提示