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