[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分析.