[20261016]光标跟踪事件2.txt

--//参考链接：https://www.0xchris.dev/posts/2026-07-30-understanding-the-cursortrace-event/
--//看看不存在的表出现的情况,顺便解析为什么这种情况并发执行出现大量的子光标。

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.

$ sql_idz.sh "select * from deptxxx\0" 3
sql_text = select * from deptxxx\0
full_hash_value(16) = EB9A82687C1923350A8F6A35B8264B04 or eb9a82687c1923350a8f6a35b8264b04
xxxxx_matching_signature(10) = 760943641004231428 or  19207687714713783044
hash_value(10) = 3089517316 or hash_value(16) = B8264B04 or b8264b04
kgl_backet(10) = 19204 or kgl_bucket(16) =  4B04 or 4b04
sql_id(16) = 0A8F6A35B8264B04 or 0a8f6a35b8264b04
sql_id(32) = 0p3va6qw2cks4
sql_id(32) =  p3va6qw2cks4
sql_id(32) =  p3va6qw2cks4
--//记下sql_id=0p3va6qw2cks4.

3.测试：
--//建立测试脚本：
$ cat aa1.txt
define hash_value=3089517316
alter session set events 'immediate trace name cursortrace level 0x41, address &hash_value';
select * from deptxxx;
select * from deptxxx;
alter session set events 'immediate trace name cursortrace level 0x80, address &hash_value';

SCOTT@book01p> @ desc deptxxx
ERROR:
ORA-04043: object deptxxx does not exist

COTT@book01p> @ aa1.txt

Session altered.

select * from deptxxx
              *
ERROR at line 1:
ORA-00942: table or view does not exist

select * from deptxxx
              *
ERROR at line 1:
ORA-00942: table or view does not exist
Session altered.

4.查看跟踪文件：
PDB#3 CUR#2 XSC 0x7f24c3719f98 CHILD#-1 CI (nil) CTX (nil)
 PARSING SQLTEXT=select * from deptxxx
 SQLHASH=b8264b04
 kksfbc begin 0x108 0 0
 No valid child pinned
 Parent 0x69e11a18(0x69e63c68) ready for search
 kksSearchChildList: no suitable child found (hash_match=0)
 SearchChildList failed ctx=(nil) xscflg=28 ctxflg=0 flg=108
 allocated new child slot bi=0x69e63140 flg=1 cld=0 hsh=b8264b04
kkshinins insert child into incomplete list bi=0x69e63140 cld=0 flg=25
 Created new child obj #0 dsfl=2002011 8003c00 xsc=0x7f24c3719f98 chd=0x69e62508 clk=0x69e11658
 Pin new child obj #0 dsfl=2002011 xsc=0x7f24c3719f98 chd=0x69e62508 clk=0x69e11658 cpn=(nil)
 kksLoadChild: reload 0 path 2 kkscsusr 109 ksuudsid 109
 kksLoadChild: reload 0 path 3 kkscsusr 109 ksuudsid 109
 Error on parse (942)

PDB#3 CUR#2 XSC 0x7f24c3719f98 CHILD#0 CI (nil) CTX (nil)
 Closing cursor
PDB#3 CUR#4 XSC 0x7f24c3719f98 CHILD#-1 CI (nil) CTX (nil)
 PARSING SQLTEXT=select * from deptxxx
 SQLHASH=b8264b04
 kksfbc begin 0x108 0 0
 No valid child pinned
 Parent 0x69e11a18(0x69e63c68) ready for search
 kksSearchChildList: no suitable child found (hash_match=0)
 SearchChildList failed ctx=(nil) xscflg=28 ctxflg=0 flg=108
 kksfbc: xsc=0x7f24c3719f98 bi=0x69e63140 child #0 is broken hsh=b8264b04
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 reuse child slot bi=0x69e63140 flg=51 cld=0 hash=b8264b04
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
kkshinins insert child into incomplete list bi=0x69e63140 cld=0 flg=35
 Created new child obj #0 dsfl=2002011 8003c00 xsc=0x7f24c3719f98 chd=0x69e62508 clk=0x69b1b348
 Pin new child obj #0 dsfl=2002011 xsc=0x7f24c3719f98 chd=0x69e62508 clk=0x69b1b348 cpn=(nil)
 kksLoadChild: reload 0 path 2 kkscsusr 109 ksuudsid 109
 kksLoadChild: reload 0 path 3 kkscsusr 109 ksuudsid 109
 Error on parse (942)
PDB#3 CUR#4 XSC 0x7f24c3719f98 CHILD#0 CI (nil) CTX (nil)
 Closing cursor
--//没有记录kkshindel操作。
--//看下划线的信息：
kksfbc: xsc=0x7f24c3719f98 bi=0x69e63140 child #0 is broken hsh=b8264b04
reuse child slot bi=0x69e63140 flg=51 cld=0 hash=b8264b04
--//发现child #0 is broken，可以理解不完整的子光标，重新使用它建立新的子光标。
--//这样就好理解并发的执行为什么这类无法正常执行的sql语句会出现大量的子光标，有一些会再使用broken的子光标，一些会建立新
--//的子光标。

SYS@book> @ sharepool/shp4 0p3va6qw2cks4 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                  KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16                   N20   KGLNAHSH KGLOBT03        KGLHDBID KGLOBT09
---------------------- ---------------- ---------------- -------------------- -------- -------- -------- ---------------- ---------------- ---------- ---------- ---------- --------- --------------------- ---------- ------------- ---------- --------
child handle address   0000000069E62508 0000000069E63C68 select * from deptxx        0        0        3 00               00                        0          0       3285      3285                  3285 3089517316 0p3va6qw2cks4      19204        0
parent handle address  0000000069E63C68 0000000069E63C68 select * from deptxx        1        0        3 0000000069E63AA8 00                     4064          0          0      4064                  4064 3089517316 0p3va6qw2cks4      19204    65535
--//子光标没有堆0，堆6，属性不完整的子光标。

4.使用bpftrace跟踪的情况：
$ cat kks.bt
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksfbc,
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksLoadChild,
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshindel,
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshinins,
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshhcins
/ $1 == 0 || pid == $1 /
{
    printf("[%s] %s Begin\n", strftime("%H:%M:%S", nsecs),func);
    //printf("[%s] %s %s pid=%d ptr0=%016lx hash_text=%s length=%d\n", strftime("%H:%M:%S", nsecs), comm,func,pid,arg0, buf(arg0,arg1),arg1);
}

uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksfbc,
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kksLoadChild,
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshindel,
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshinins,
uretprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kkshhcins
/ $1 == 0 || pid == $1 /
{
    //printf("[%s] %s %s pid=%d return hash_value=%016lx %lu\n", strftime("%H:%M:%S", nsecs), func,comm,pid, retval, retval);
    //printf("[%s] %s pid=%d return hash_value=%016lx %lu\n", strftime("%H:%M:%S", nsecs), func,pid, retval, retval);
    printf("[%s] %s End\n", strftime("%H:%M:%S", nsecs),func);
}

# bpftrace kks.bt 3171
Attached 10 probes
[08:48:24] kksfbc Begin
[08:48:24] kkshinins Begin
[08:48:24] kkshinins End
[08:48:24] kksLoadChild Begin
[08:48:24] kksLoadChild End
[08:48:24] kksLoadChild Begin
[08:48:24] kkshindel Begin
[08:48:24] kkshindel End
--//前面的跟踪缺少kkshindel函数，不知道为什么？