[20261106]光标跟踪事件11.txt

--//做一个补充测试：
--//1.确定链接https://www.0xchris.dev/posts/2026-06-08-child-cursor-scan-mechanics--an-investigation/里面提到的参数
--//  _xds_max_child_cursors指总的子光标数量,还是指链接里面的数量。
--//2.测试存在哈希冲突与不冲突的情况(pdbname与cdbname通过计算的哈希值的最后1位),两种情况下是否存在性能差异.

1.环境：
SYS@book> @ 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.

SYS@book> @ hidez _xds_max_child_cursors
SYS@book> @ pr
==============================
NUM                           : 3633
N_HEX                         :   E31
CON_ID                        : 0
NAME                          : _xds_max_child_cursors
DESCRIPTION                   : Maximum number of XDS user-specific child cursors
DEFAULT_VALUE                 : TRUE
SESSION_VALUE                 : 100
SYSTEM_VALUE                  : 100
ISSES_MODIFIABLE              : FALSE
ISSYS_MODIFIABLE              : IMMEDIATE
PL/SQL procedure successfully completed.

SYS@book> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 BOOK01P                        READ WRITE NO
--//pdbname与cdbname通过计算的哈希值的最后1位(16进制)不同。

2.建立测试脚本以及测试环境：
--//分别在cdb$root,book02p下建立表t.
--//cdb$root下以sys用户建立如下表。
SYS@book> create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
Table created.

SYS@book> alter table t modify ( id  not null );
Table altered.

SYS@book> create unique index i_t_id on t(id);
Index created.

--//pdb=book01p下以scott用户建立表以及索引过程略。

$ cat m15.txt
connect / as sysdba
--alter session set session_cached_cursors=0;
set verify off
alter session set optimizer_index_cost_adj = &&2;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        SELECT COUNT(NAME) INTO v_id FROM T WHERE ID = 1;
    end loop;
end ;
/

connect scott/book@book01p
--alter session set session_cached_cursors=0;
set verify off
alter session set optimizer_index_cost_adj = &&2;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        SELECT COUNT(NAME) INTO v_id FROM T WHERE ID = 1;
    end loop;
end ;
/
quit
--//sql_id=494nuxkcrxsz3.

$ cat m15a.txt
alter session set session_cached_cursors=0;
set verify off
alter session set optimizer_index_cost_adj = &&2;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        SELECT COUNT(NAME) INTO v_id FROM T WHERE ID = 1;
    end loop;
end ;
/

3.测试：

$ seq 64 | xargs -IQ sqlplus -s -l /nolog  @m15.txt 6 Q >/dev/null

--//这样相当于建立128个子光标，con_id相互交错。

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 64 > /dev/null
real    0m10.091s
user    0m0.227s
sys     0m0.011s

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 50 > /dev/null
real    0m6.448s
user    0m0.216s
sys     0m0.008s

--//有点意外，选择optimizer_index_cost_adj = 50的最快,在链表开头.
--//这个参数_xds_max_child_cursors是指总的子光标数量超过100，插入链表结尾，小于等于时插入链接开头。

--//关闭pdb数据库。
SYS@book> ALTER PLUGGABLE DATABASE book01p CLOSE IMMEDIATE;
Pluggable database altered.

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 64 > /dev/null
real    0m10.094s
user    0m0.228s
sys     0m0.012s

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 50 > /dev/null
real    0m6.432s
user    0m0.261s
sys     0m0.013s
--//可以发现关闭pdbname=book01p的数据库,@m15a.txt 2e5 64 的执行时间与前面的测试一样。因为pdbname与cdbname经过
--//kpdbNameHash函数计算最后1位(16进制)不同，它们分别链接不同的链表上。

3.继续:
--//修改pdbname=book02p,这样pdbname与cdbname通过计算的哈希值的最后1位(16进制)相同.过程略.
SYS@book> alter system flush shared_pool;
System altered.

--//修改m15.txt 脚本的连接串.
$ cat m15.txt
connect / as sysdba
--alter session set session_cached_cursors=0;
set verify off
alter session set optimizer_index_cost_adj = &&2;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        SELECT COUNT(NAME) INTO v_id FROM T WHERE ID = 1;
    end loop;
end ;
/

connect scott/book@127.0.0.1:1521/book02p
--alter session set session_cached_cursors=0;
set verify off
alter session set optimizer_index_cost_adj = &&2;
declare
v_id number;
v_d date;
l_count PLS_INTEGER;
begin
    for i in 1 .. &&1 loop
        SELECT COUNT(NAME) INTO v_id FROM T WHERE ID = 1;
    end loop;
end ;
/
quit

--//重复前面的测试：

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 64 > /dev/null
real    0m12.432s
user    0m0.198s
sys     0m0.012s

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 50 > /dev/null
real    0m6.779s
user    0m0.205s
sys     0m0.007s
--//存在冲突的情况下差别并不是非常明显，不过执行@m15a.txt 2e5 64比原来慢了2秒多一点。

--//关闭pdb数据库。
SYS@book> ALTER PLUGGABLE DATABASE book02p CLOSE IMMEDIATE;
Pluggable database altered.

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 64 > /dev/null
real    0m12.344s
user    0m0.208s
sys     0m0.014s
--//即使关闭pdb数据库。cdb下的密集执行@m15a.txt 2e5 64并没有变化。
SYS@book> @ m15a.txt 1 64
Session altered.
Session altered.
PL/SQL procedure successfully completed.


3231 oracle_3231_boo -> kkscsSearchChildList: 0x7f5e0afaa060, 0x7fff52c572f0, xsc=0x7f5e059a0100, flag=0x108, ...
3231 oracle_3231_boo    kkscsSearchChildList: Parent ready for search (kglkdadr=0x6d8bc8c8, kglhdpar=0x63894490)
3231 oracle_3231_boo -> kpdbNameHash: 0x7e71c94c, 0x8 CDB$ROOT
3231 oracle_3231_boo -> kpdbNameHash: 0x7e71c94c, 0x8 CDB$ROOT
3231 oracle_3231_boo    kpdbNameHash: pdb_name is CDB$ROOT (hsh=0x1eee6512)
3231 oracle_3231_boo <- kpdbNameHash: 0x1eee6512 (518939922)
3231 oracle_3231_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6fc27410, cur_hbk=0x7fff52c56668, cs=0x7fff52c56670, ...
3231 oracle_3231_boo    kkshGetNextChild: *ht=0x6fc27430 (pdbuid=0)
3231 oracle_3231_boo    kkshGetNextChild: *cur_hbk=0x6fc27490
3231 oracle_3231_boo    kkshGetNextChild: cshsl=0x6da91610[0x6fc27490,0x6da90eb0]
3231 oracle_3231_boo    kkshGetNextChild: cshbk=0x6da90eb0
3231 oracle_3231_boo    kkshGetNextChild: prev: cs=0, cno=0
3231 oracle_3231_boo    kkshGetNextChild: curr: cs=0x6da915c0, cno=99
3231 oracle_3231_boo    kkshGetNextChild: next: cs=0x6da90e60, cno=98
3231 oracle_3231_boo <- kkshGetNextChild: ret=0x0 (0)

3231 oracle_3231_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6fc27410, cur_hbk=0x7fff52c56668, cs=0x7fff52c56670, ...
3231 oracle_3231_boo    kkshGetNextChild: *ht=0x6fc27430 (pdbuid=1)
3231 oracle_3231_boo    kkshGetNextChild: *cur_hbk=0x6fc27490
3231 oracle_3231_boo    kkshGetNextChild: cshsl=0x6da90eb0[0x6da91610,0x6ddc5f18]
3231 oracle_3231_boo    kkshGetNextChild: cshbk=0x6ddc5f18
3231 oracle_3231_boo    kkshGetNextChild: prev: cs=0x6da915c0, cno=99
3231 oracle_3231_boo    kkshGetNextChild: curr: cs=0x6da90e60, cno=98
3231 oracle_3231_boo    kkshGetNextChild: next: cs=0x6ddc5ec8, cno=97
3231 oracle_3231_boo <- kkshGetNextChild: ret=0x0 (0)

3231 oracle_3231_boo -> kkscsCheckCursor: 0x7f5e0afaa060, 0x7fff52c572f0, xsc=0x7f5e059a0100, 0x108, 0x0, 0x7fff52c57270, ...
3231 oracle_3231_boo    kkscsCheckCursor: next child is #98 (cs=0x6da90e60, pdbuid=1)
3231 oracle_3231_boo -> kkscsPruneChild: 0x7f5e0afaa060, xsc=0x7f5e059a0100, cs=0x6da90e60, flg=0x7fff52c572f0
3231 oracle_3231_boo    kkscsPruneChild: Child was pruned
3231 oracle_3231_boo <- kkscsPruneChild: ret=0x1 (1)
3231 oracle_3231_boo <- kkscsCheckCursor: ret=0x0 (0)

3231 oracle_3231_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6fc27410, cur_hbk=0x7fff52c56668, cs=0x7fff52c56670, ...
3231 oracle_3231_boo    kkshGetNextChild: *ht=0x6fc27430 (pdbuid=0)
3231 oracle_3231_boo    kkshGetNextChild: *cur_hbk=0x6fc27490
3231 oracle_3231_boo    kkshGetNextChild: cshsl=0x6ddc5f18[0x6da90eb0,0x6ddc57b8]
3231 oracle_3231_boo    kkshGetNextChild: cshbk=0x6ddc57b8
3231 oracle_3231_boo    kkshGetNextChild: prev: cs=0x6da90e60, cno=98
3231 oracle_3231_boo    kkshGetNextChild: curr: cs=0x6ddc5ec8, cno=97
3231 oracle_3231_boo    kkshGetNextChild: next: cs=0x6ddc5768, cno=96
3231 oracle_3231_boo <- kkshGetNextChild: ret=0x0 (0)
...
3231 oracle_3231_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6fc27410, cur_hbk=0x7fff52c56668, cs=0x7fff52c56670, ...
3231 oracle_3231_boo    kkshGetNextChild: *ht=0x6fc27430 (pdbuid=1)
3231 oracle_3231_boo    kkshGetNextChild: *cur_hbk=0x6fc27490
3231 oracle_3231_boo    kkshGetNextChild: cshsl=0x6b310568[0x6b7627b8,0x6b310cc8]
3231 oracle_3231_boo    kkshGetNextChild: cshbk=0x6b310cc8
3231 oracle_3231_boo    kkshGetNextChild: prev: cs=0x6b762768, cno=125
3231 oracle_3231_boo    kkshGetNextChild: curr: cs=0x6b310518, cno=126
3231 oracle_3231_boo    kkshGetNextChild: next: cs=0x6b310c78, cno=127
3231 oracle_3231_boo <- kkshGetNextChild: ret=0x0 (0)

3231 oracle_3231_boo -> kkscsCheckCursor: 0x7f5e0afaa060, 0x7fff52c572f0, xsc=0x7f5e059a0100, 0x108, 0x0, 0x7fff52c57270, ...
3231 oracle_3231_boo    kkscsCheckCursor: next child is #126 (cs=0x6b310518, pdbuid=1)
3231 oracle_3231_boo <- kkscsCheckCursor: ret=0x71212d20 (1897999648)

3231 oracle_3231_boo    kkscsSearchChildList: complete: inspected 64 children in 1303428 ns (1303 us)
3231 oracle_3231_boo <- kkscsSearchChildList: ret=0x71212d20 (1897999648)

--//虽然pdbname数据库已经关闭，但是对应的子光标还在，堆0，堆6已经不存在了，依旧探查导致执行时间并没有缩小。

4.补充：
--//顺便记录自己测试时打开bpftrace的脚本监测遇到的情况：

# ./kkscsSearchChildList.bt 3231 verbose
Attached 14 probes
Tracing... Hit ^C to stop.
^C
--//进程号3231已经不存在了。

$ time sqlplus  -s -l / as sysdba  @m15a.txt 2e5 64 > /dev/null
real    1m54.469s
user    0m0.202s
sys     0m0.010s

--//执行时间明显增加。