[20261013]光标跟踪事件.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.

SCOTT@book01p> select * from dept where deptno=61;
no rows selected

SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- ---------- ------------------- -----------
 602167305 gm1yr8wjy8q09            0      22537  23e45809  2026-10-13 16:56:24    16777216
--//HASH_VALUE=602167305
--//缺省optimizer_index_cost_adj=100,仅仅建立CHILD_NUMBER=0.

2.测试：
--//建立测试脚本：
$ cat aaa.txt
define hash_value=602167305
alter session set events 'immediate trace name cursortrace level 0x41, address &hash_value';
alter session set optimizer_index_cost_adj=8;
select * from dept where deptno=61;
alter session set optimizer_index_cost_adj=9;
select * from dept where deptno=61;
alter session set optimizer_index_cost_adj=9;
select * from dept where deptno=61;
alter session set events 'immediate trace name cursortrace level 0x80, address &hash_value';
--//执行3次，最后2次optimizer_index_cost_adj=9，应该共享相同光标。

SCOTT@book01p> @ aaa.txt
Session altered.
Session altered.
no rows selected
Session altered.
no rows selected
Session altered.
no rows selected
Session altered.

3.查看跟踪文件：
PDB#3 CUR#1 XSC 0x7fe656d3c2e0 CHILD#-1 CI (nil) CTX (nil)
 PARSING SQLTEXT=select * from dept where deptno=61
 SQLHASH=23e45809
 kksfbc begin 0x108 0 0
 No valid child pinned
 Parent 0x6fb02460(0x69c74a50) ready for search
 kksCheckCursor: next child is #0
 kksCheckCursor: pinning child #0 in shared mode 0x6fb02550 0x69643df8
 kksUnlockChild: releasing child
 Failed sharing : 4
 kksSearchChildList: no suitable child found (hash_match=1)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 SearchChildList failed ctx=(nil) xscflg=20 ctxflg=0 flg=108
 allocated new child slot bi=0x696f6cd0 flg=1 cld=1 hsh=23e45809
kkshinins insert child into incomplete list bi=0x696f6cd0 cld=1 flg=25
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Created new child obj #1 dsfl=2002011 8003c00 xsc=0x7fe656d3c2e0 chd=0x6bd848d8 clk=0x6f810ad8
 Pin new child obj #1 dsfl=2002011 xsc=0x7fe656d3c2e0 chd=0x6bd848d8 clk=0x6f810ad8 cpn=(nil)
 kksLoadChild: reload 0 path 2 kkscsusr 109 ksuudsid 109
 ~~~~~~~~~~~~~
 kksLoadChild: reload 0 path 3 kkscsusr 109 ksuudsid 109
 ~~~~~~~~~~~~~~
 Child creation successful xsc=0x7fe656d3c2e0 chd=0x6bd848d8 cpn=0x6b58db88 sta=0 bi=0x696f6cd0 cld=1
 Add new child to parent list pi=0x69581468 psn=1 bi=0x696f6cd0 cld=1 flg=35
kkshindel remove child from incomplete list bi=0x696f6cd0 cld=1 flg=30
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 kkshhcins insert child into hash table bi=0x696f6cd0 cld=1 flg=38
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Downgrading child pin to share
 kksfbc begin 0x102 1 1
 Cursor about get executed
 kksfbc begin 0x1 1 1
PDB#3 CUR#1 XSC 0x7fe656d3c2e0 CHILD#1 CI 0x697a7e58 CTX (nil)
 Closing cursor

$ ./lookup.awk  kksfbc
kksfbc : kernel compile shared objects (cursor) find bound cursor
 
--//1.执行kksCheckCursor看看是否有合适的子光标，如果没执行有kksSearchChildList，还是没有
--//2.执行kkshinins insert child into incomplete list，插入子光标到incomplete list,建立新的子光标，然后执行kksLoadChild
--//加载(执行2次?)。
--//3.执行kkshindel remove child from incomplete list，删除子光标从incomplete list。
--//4.执行kkshhcins insert child into hash table，插入子光标进入hash table。

 PARSING SQLTEXT=select * from dept where deptno=61
 SQLHASH=23e45809
 kksfbc begin 0x108 0 0
 No valid child pinned
 Parent 0x6f810ad8(0x69c74a50) ready for search
 kksCheckCursor: next child is #1
 kksCheckCursor: pinning child #1 in shared mode 0x6fb02280 0x6bd848d8
 kksUnlockChild: releasing child
 Failed sharing : 4
 kksCheckCursor: next child is #0
Child was pruned
 kksSearchChildList: no suitable child found (hash_match=1)
 SearchChildList failed ctx=(nil) xscflg=20 ctxflg=0 flg=108
 allocated new child slot bi=0x696f7430 flg=1 cld=2 hsh=23e45809
kkshinins insert child into incomplete list bi=0x696f7430 cld=2 flg=25
 Created new child obj #2 dsfl=2002011 8003c00 xsc=0x7fe656d28d10 chd=0x6ff373d8 clk=0x6fb02550
 Pin new child obj #2 dsfl=2002011 xsc=0x7fe656d28d10 chd=0x6ff373d8 clk=0x6fb02550 cpn=(nil)
 kksLoadChild: reload 0 path 2 kkscsusr 109 ksuudsid 109
 kksLoadChild: reload 0 path 3 kkscsusr 109 ksuudsid 109
 Child creation successful xsc=0x7fe656d28d10 chd=0x6ff373d8 cpn=0x6b58db88 sta=0 bi=0x696f7430 cld=2
 Add new child to parent list pi=0x69581468 psn=2 bi=0x696f7430 cld=2 flg=35
kkshindel remove child from incomplete list bi=0x696f7430 cld=2 flg=30
 kkshhcins insert child into hash table bi=0x696f7430 cld=2 flg=38
 Downgrading child pin to share
 kksfbc begin 0x102 1 1
 Cursor about get executed
 kksfbc begin 0x1 1 1
PDB#3 CUR#1 XSC 0x7fe656d28d10 CHILD#2 CI 0x699006c8 CTX (nil)
 Closing cursor
--//基本与前面类似，仅仅kksCheckCursor从child #1,然后child is #0,不合适建立新子光标child #2.

 PARSING SQLTEXT=select * from dept where deptno=61
 SQLHASH=23e45809
 kksfbc begin 0x108 0 0
 No valid child pinned
 Parent 0x6f810ad8(0x69c74a50) ready for search
 kksCheckCursor: next child is #2
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 kksCheckCursor: pinning child #2 in shared mode 0x6fb02550 0x6ff373d8
 Calling for auth
 kksfbc begin 0x102 1 1
 Cursor about get executed
 kksfbc begin 0x1 1 1
PDB#3 CUR#1 XSC 0x7fe656d28d10 CHILD#2 CI 0x699006c8 CTX (nil)
 Closing cursor
--//第3次执行，kksCheckCursor检查光标兼容，没有产生新的子光标，直接执行。