[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分析.
--//记录表不存在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分析.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2021-09-01 [20210831]bbed读取数据块6.txt