[20261102]光标跟踪事件9.txt
--//12c开始pdb，引入pdb导致各个pdb下如何sql语句相同，生成的sql_id相同以及hash值也相同，这样如果多个pdb运行，如果执行的
--//Sql语句句相同，再加上如果遇到出现大量子光标情况，岂不是该sql语句出现大量子光标，查询兼容的子光标不是花费很大，这些
--//都是我开始转向pdb的想法，前段时间看了链接：
--//https://fritshoogland.wordpress.com/2020/04/04/oracle-library-cache-cursor-child-generation/
--//https://fritshoogland.wordpress.com/2020/04/03/oracle-multi-tenant-and-library-cache-isolation/
--//而其他对象比如表，hash的计算加入pdb字符串，基本不会相同的hash值。
--//按照里面的提示，直接转抄如下：
The first one is that with pluggable databases, a child has the container-id in which the child is generated as a
property. During the inspection of the child list, the container-id of the child must match the container-id of the
executing session, otherwise the child is skipped. This prevents from getting blocked from sessions across containers.
Please mind the library cache hash table and the parent handle are shared between containers, although waiting for these
should be negligible. See Oracle multi-tenant and library cache isolation.
--//第一种方法是使用可插拔数据库，子程序具有容器id，其中子程序作为属性生成。 在检查子列表时，子的容器-id必须与执行会话的
--//容器-id匹配，否则跳过子.这防止了从跨容器的会话中被阻塞。请注意库缓存哈希表，父句柄在容器之间共享，尽管等待这些应该可
--//以忽略不计。 参见Oracle多租户和库缓存隔离.
https://www.0xchris.dev/posts/2026-06-08-child-cursor-scan-mechanics--an-investigation/
To better understand this behavior, I examined the kkscs structures in more detail and discovered a second list linking
them via "next" and "prev" pointers, forming a doubly linked list. Internally, this list is referred to as cshsl (child
cursor hash list?), and there are 16 such lists per parent cursor, each associated with a hash bucket in a hash table
pointed to by the ht pointer.
为更深入地理解这一行为，我对 kkscs 结构进行了更细致的分析，进而发现第二个列表——该列表通过"next"与"prev"指针将各结构相
连，从而构成一个双向链表。在内部实现中，该列表被命名为 cshsl(child cursor hash list？)，每个父游标下均存在 16 个此类列表
，且每个列表均与由 ht 指针指向的哈希表中的某个哈希桶相关联。
The child cursor scan code path in kkscsSearchChildList (not shown here) computes a hash value from the pdb name
(function kpdbNameHash) and uses it to derive a bucket index in the ht hash table via a modulo-16 operation. This means
child cursors created in different pdbs are distributed across 16 child cursor "sublists" according to the pdb name
hash.
kkscsSearchChildList 函数中的子游标扫描代码路径(此处未显示)根据 pdb 名称计算一个哈希值(通过函数 kpdbNameHash)，并利用该
哈希值通过 modulo-16 运算推导出 ht 哈希表中的桶索引。这意味着，在不同 pdb 中创建的子游标，将根据 pdb 名称的哈希值分布到
16 个子游标"子列表"中。
--//按照上面的解析该列表存在父游标堆0里面，存在1个16个子游标列表，并且各个子列表通过"next"与"prev"指针将各结构相连，从而
--//构成一个双向链表。也就是既有hash table也有双向链表的情况，不知道11g会是什么情况。
--//前面测试pdbname计算的hash值16进制后1位如果存在冲突的情况，下面测试不冲突的情况。
--//修改pdbname=BOOK02P为BOOK01P，过程略。
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.
$ cat dc/ora_hash1.txt
select
ora_hash('&&1',4294967295,&2) n20
,to_char(ora_hash('&1',4294967295,&2),'FM0xxxxxxxxxxxxxxx') c20
from dual;
SCOTT@book01p> @ dc/ora_hash1.txt CDB$ROOT 0
N20 C20
--------------------- --------------------
518939922 000000001eee6512
--//最后1位是2。不知道如果某个pdbname经过hash值计算正好最后1位等于2(16进制)，会出现什么情况。
SYS@book> @ dc/ora_hash1.txt BOOK01P 0
N20 C20
--------------------- --------------------
1011216667 000000003c45f11b
--//pdb=BOOK01P，16进制最后1位是b，正好与CDB$ROOThash不冲突，会出现什么情况呢？
2.建立测试脚本以及测试环境：
--//分别在cdb$root,book01p下建立表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 m14.txt
--alter session set session_cached_cursors=0;
set verify off
exec dbms_lock.sleep(2)
alter session set optimizer_index_cost_adj = &&1;
SELECT COUNT(NAME) FROM T WHERE ID=1;
/
/
/
/
/
quit
$ cat aa5.txt
alter session set session_cached_cursors=0;
alter session set optimizer_index_cost_adj = &&1;
SELECT COUNT(NAME) FROM T WHERE ID=1;
alter session set optimizer_index_cost_adj = 100;
--//同时执行如下，测试前刷新共享池：
seq 5 | xargs -IQ sqlplus -s -l scott/book@book01p @m14.txt Q > /dev/null &
seq 5 | xargs -IQ sqlplus -s -l / as sysdba @m14.txt Q > /dev/null
--//这样相当于建立10个子光标，con_id相互交错。
SYS@book> select con_id,sql_id,child_number,hash_value,sql_text,LAST_ACTIVE_TIME from v$sql where sql_id='5zfc9khsnyp90' order by 3;
CON_ID SQL_ID CHILD_NUMBER HASH_VALUE SQL_TEXT LAST_ACTIVE_TIME
------ ------------- ------------ ---------- ------------------------------------ -------------------
1 5zfc9khsnyp90 0 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:28
3 5zfc9khsnyp90 1 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:29
1 5zfc9khsnyp90 2 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:31
3 5zfc9khsnyp90 3 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:31
1 5zfc9khsnyp90 4 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:33
3 5zfc9khsnyp90 5 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:33
1 5zfc9khsnyp90 6 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:35
3 5zfc9khsnyp90 7 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:35
1 5zfc9khsnyp90 8 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:38
3 5zfc9khsnyp90 9 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 16:21:38
10 rows selected.
3.测试：
SYS@book> @ aa5.txt 1
Session altered.
Session altered.
COUNT(NAME)
-----------
1
Session altered.
# ./kkscsSearchChildList.bt 3127 verbose
Attached 14 probes
Tracing... Hit ^C to stop.
3127 oracle_3127_boo -> kkscsSearchChildList: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, flag=0x108, ...
3127 oracle_3127_boo kkscsSearchChildList: Parent ready for search (kglkdadr=0x6b2a7bf8, kglhdpar=0x6d43e560)
3127 oracle_3127_boo -> kpdbNameHash: 0x7e702d1c, 0x8 CDB$ROOT
3127 oracle_3127_boo -> kpdbNameHash: 0x7e702d1c, 0x8 CDB$ROOT
3127 oracle_3127_boo <- kpdbNameHash: 0x1eee6512 (518939922)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3127 oracle_3127_boo <- kpdbNameHash: 0x1eee6512 (518939922)
3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6d4b38e0, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo kkshGetNextChild: *ht=0x6d4b3900 (pdbuid=1)
3127 oracle_3127_boo kkshGetNextChild: *cur_hbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: cshsl=0x643299d0[0x6d4b3960,0x6ff1cd90]
3127 oracle_3127_boo kkshGetNextChild: cshbk=0x6ff1cd90
3127 oracle_3127_boo kkshGetNextChild: prev: cs=0, cno=0
3127 oracle_3127_boo kkshGetNextChild: curr: cs=0x64329980, cno=8
3127 oracle_3127_boo kkshGetNextChild: next: cs=0x6ff1cd40, cno=6
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)
3127 oracle_3127_boo -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo kkscsCheckCursor: next child is #8 (cs=0x64329980, pdbuid=1)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x0 (0)
3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6d4b38e0, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo kkshGetNextChild: *ht=0x6d4b3900 (pdbuid=1)
3127 oracle_3127_boo kkshGetNextChild: *cur_hbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: cshsl=0x6ff1cd90[0x643299d0,0x69be07a0]
3127 oracle_3127_boo kkshGetNextChild: cshbk=0x69be07a0
3127 oracle_3127_boo kkshGetNextChild: prev: cs=0x64329980, cno=8
3127 oracle_3127_boo kkshGetNextChild: curr: cs=0x6ff1cd40, cno=6
3127 oracle_3127_boo kkshGetNextChild: next: cs=0x69be0750, cno=4
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)
3127 oracle_3127_boo -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo kkscsCheckCursor: next child is #6 (cs=0x6ff1cd40, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x6ff1cd40, flg=0x7fff0f0df600
3127 oracle_3127_boo kkscsPruneChild: Child was pruned
3127 oracle_3127_boo <- kkscsPruneChild: ret=0x1 (1)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x0 (0)
3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6d4b38e0, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo kkshGetNextChild: *ht=0x6d4b3900 (pdbuid=1)
3127 oracle_3127_boo kkshGetNextChild: *cur_hbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: cshsl=0x69be07a0[0x6ff1cd90,0x6d3a8ef8]
3127 oracle_3127_boo kkshGetNextChild: cshbk=0x6d3a8ef8
3127 oracle_3127_boo kkshGetNextChild: prev: cs=0x6ff1cd40, cno=6
3127 oracle_3127_boo kkshGetNextChild: curr: cs=0x69be0750, cno=4
3127 oracle_3127_boo kkshGetNextChild: next: cs=0x6d3a8ea8, cno=2
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)
3127 oracle_3127_boo -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo kkscsCheckCursor: next child is #4 (cs=0x69be0750, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x69be0750, flg=0x7fff0f0df600
3127 oracle_3127_boo kkscsPruneChild: Child was pruned
3127 oracle_3127_boo <- kkscsPruneChild: ret=0x1 (1)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x0 (0)
3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6d4b38e0, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo kkshGetNextChild: *ht=0x6d4b3900 (pdbuid=1)
3127 oracle_3127_boo kkshGetNextChild: *cur_hbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: cshsl=0x6d3a8ef8[0x69be07a0,0x6d4b3cd0]
3127 oracle_3127_boo kkshGetNextChild: cshbk=0x6d4b3cd0
3127 oracle_3127_boo kkshGetNextChild: prev: cs=0x69be0750, cno=4
3127 oracle_3127_boo kkshGetNextChild: curr: cs=0x6d3a8ea8, cno=2
3127 oracle_3127_boo kkshGetNextChild: next: cs=0x6d4b3c80, cno=0
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)
3127 oracle_3127_boo -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo kkscsCheckCursor: next child is #2 (cs=0x6d3a8ea8, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x6d3a8ea8, flg=0x7fff0f0df600
3127 oracle_3127_boo kkscsPruneChild: Child was pruned
3127 oracle_3127_boo <- kkscsPruneChild: ret=0x1 (1)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x0 (0)
3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x6d4b38e0, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo kkshGetNextChild: *ht=0x6d4b3900 (pdbuid=1)
3127 oracle_3127_boo kkshGetNextChild: *cur_hbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: cshsl=0x6d4b3cd0[0x6d3a8ef8,0x6d4b3960]
3127 oracle_3127_boo kkshGetNextChild: cshbk=0x6d4b3960
3127 oracle_3127_boo kkshGetNextChild: prev: cs=0x6d3a8ea8, cno=2
3127 oracle_3127_boo kkshGetNextChild: curr: cs=0x6d4b3c80, cno=0
3127 oracle_3127_boo kkshGetNextChild: next: *cur_hbk=0x6d4b3960 (end of list)
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)
3127 oracle_3127_boo -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo kkscsCheckCursor: next child is #0 (cs=0x6d4b3c80, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x6d4b3c80, flg=0x7fff0f0df600
3127 oracle_3127_boo <- kkscsPruneChild: ret=0x0 (0)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x6ce18920 (1826720032)
3127 oracle_3127_boo kkscsSearchChildList: complete: inspected 5 children in 172336 ns (172 us)
3127 oracle_3127_boo <- kkscsSearchChildList: ret=0x6ce18920 (1826720032)
--//可以发现在pdbname哈希不冲突的情况，bpftrace的kkscsSearchChildList.bt脚本记录扫描子光标8，6，4，2，0.
--//调用kkshGetNextChild的测试明显比前面hash冲突的情况下减少一半。
--//关于kkscsSearchChildList.bt脚本可以在
--//https://www.0xchris.dev/posts/2026-06-08-child-cursor-scan-mechanics--an-investigation/找到相关下载链接。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3187399/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
- [20261013]光标跟踪事件.txt
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
