ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20261102]光标跟踪事件8.txt

[20261102]光标跟踪事件8.txt

原创 Oracle 作者：lfree 时间：2026-11-02 20:17:27 0 删除 编辑

[20261102]光标跟踪事件8.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会是什么情况。

--//按照上面信息提示，不同con_id的pdbname计算的hash值16进制后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.

--//通过kpdbNameHash计算hash值，仔细看反汇编kkshchv

   0x0000000014fda5fe <+78>:    call   0x14eaa160 <kpdbNameHash>
   0x0000000014fda603 <+83>:    mov    %eax,(%r14)
   0x0000000014fda606 <+86>:    xor    %edi,%edi
   0x0000000014fda608 <+88>:    movzbl 0x10(%rbp),%eax
--//实际上仅仅取16进制的最后1位。

$ objdump  --disassemble=kpdbNameHash $(which oracle)
/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:     file format elf64-x86-64
Disassembly of section .init:
Disassembly of section .plt:
Disassembly of section .text:
Disassembly of section text.unlikely:
Disassembly of section text.hot:
0000000014eaa160 <kpdbNameHash>:
    14eaa160:   66 90                   xchg   %ax,%ax
    14eaa162:   55                      push   %rbp
    14eaa163:   48 89 e5                mov    %rsp,%rbp
    14eaa166:   40 84 f6                test   %sil,%sil
    14eaa169:   74 0f                   je     14eaa17a <kpdbNameHash+0x1a>
    14eaa16b:   40 0f b6 f6             movzbl %sil,%esi
    14eaa16f:   33 d2                   xor    %edx,%edx
    14eaa171:   48 89 ec                mov    %rbp,%rsp
    14eaa174:   5d                      pop    %rbp
    14eaa175:   e9 f6 63 47 00          jmpq   15320570 <kgghash>
    14eaa17a:   33 c0                   xor    %eax,%eax
    14eaa17c:   48 89 ec                mov    %rbp,%rsp
    14eaa17f:   5d                      pop    %rbp
    14eaa180:   c3                      retq
    14eaa181:   0f 1f 84 00 00 00 00    nopl   0x0(%rax,%rax,1)
    14eaa188:   00
    14eaa189:   0f 1f 80 00 00 00 00    nopl   0x0(%rax)
Disassembly of section .fini:
--//kpdbNameHash的本质就是调用kgghash函数。

$ 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进制)，会出现什么情况。
SCOTT@book01p> @ dc/ora_hash1.txt BOOK02P 0
                  N20 C20
--------------------- --------------------
           3433827810 00000000ccac0de2

--//最后1位是2。pdb=BOOK02P，正好与CDB$ROOT存在hash冲突，会出现什么情况呢？
--//注：pdbname已经从book01p修改为book02p。

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=book02p下以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@127.0.0.1:1521/book02p @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 LAST_ACTIVE_TIME;
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 15:25:06
     3 5zfc9khsnyp90            1 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:06
     1 5zfc9khsnyp90            2 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:08
     3 5zfc9khsnyp90            3 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:08
     1 5zfc9khsnyp90            4 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:10
     3 5zfc9khsnyp90            5 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:11
     1 5zfc9khsnyp90            6 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:13
     3 5zfc9khsnyp90            7 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:13
     1 5zfc9khsnyp90            8 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:15
     3 5zfc9khsnyp90            9 2974766368 SELECT COUNT(NAME) FROM T WHERE ID=1 2026-11-02 15:25:15
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=0x6fd8ceb8, kglhdpar=0x69399e98)
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=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x6930bd80[0x69399068,0x6930b620]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x6930b620
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0, cno=0
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x6930bd30, cno=9
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x6930b5d0, cno=8
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)

3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x6930b620[0x6930bd80,0x693147e0]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x693147e0
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x6930bd30, cno=9
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x6930b5d0, cno=8
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69314790, cno=7
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=0x6930b5d0, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x6930b5d0, 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=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x693147e0[0x6930b620,0x69314080]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x69314080
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x6930b5d0, cno=8
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69314790, cno=7
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69314030, cno=6
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)

3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x69314080[0x693147e0,0x6931ce10]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x6931ce10
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x69314790, cno=7
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69314030, cno=6
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x6931cdc0, cno=5
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=0x69314030, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x69314030, 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=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x6931ce10[0x69314080,0x6931c6b0]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x6931c6b0
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x69314030, cno=6
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x6931cdc0, cno=5
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x6931c660, cno=4
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)

3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x6931c6b0[0x6931ce10,0x69326ef0]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x69326ef0
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x6931cdc0, cno=5
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x6931c660, cno=4
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69326ea0, cno=3
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=0x6931c660, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x6931c660, 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=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x69326ef0[0x6931c6b0,0x69326790]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x69326790
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x6931c660, cno=4
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69326ea0, cno=3
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69326740, cno=2
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)

3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x69326790[0x69326ef0,0x693997e0]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x693997e0
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x69326ea0, cno=3
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69326740, cno=2
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69399790, cno=1
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=0x69326740, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x69326740, 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 -> kkscsCheckCursor: 0x7f1949e3c060, 0x7fff0f0df600, xsc=0x7f19444a1460, 0x108, 0x0, 0x7fff0f0df580, ...
3127 oracle_3127_boo    kkscsCheckCursor: next child is #2 (cs=0x69326740, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x69326740, 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=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x693997e0[0x69326790,0x693993d8]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x693993d8
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x69326740, cno=2
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69399790, cno=1
3127 oracle_3127_boo    kkshGetNextChild: next: cs=0x69399388, cno=0
3127 oracle_3127_boo <- kkshGetNextChild: ret=0x0 (0)

3127 oracle_3127_boo -> kkshGetNextChild: hsh=0x1eee6512, ht=0x69398fe8, cur_hbk=0x7fff0f0de978, cs=0x7fff0f0de980, ...
3127 oracle_3127_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3127 oracle_3127_boo    kkshGetNextChild: *cur_hbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: cshsl=0x693993d8[0x693997e0,0x69399068]
3127 oracle_3127_boo    kkshGetNextChild: cshbk=0x69399068
3127 oracle_3127_boo    kkshGetNextChild: prev: cs=0x69399790, cno=1
3127 oracle_3127_boo    kkshGetNextChild: curr: cs=0x69399388, cno=0
3127 oracle_3127_boo    kkshGetNextChild: next: *cur_hbk=0x69399068 (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=0x69399388, pdbuid=1)
3127 oracle_3127_boo -> kkscsPruneChild: 0x7f1949e3c060, xsc=0x7f19444a1460, cs=0x69399388, flg=0x7fff0f0df600
3127 oracle_3127_boo <- kkscsPruneChild: ret=0x0 (0)
3127 oracle_3127_boo <- kkscsCheckCursor: ret=0x685c7888 (1750890632)

3127 oracle_3127_boo    kkscsSearchChildList: complete: inspected 5 children in 270475 ns (270 us)
3127 oracle_3127_boo <- kkscsSearchChildList: ret=0x685c7888 (1750890632)
--//可以发现在pdb哈希冲突的情况，bpftrace的kkscsSearchChildList.bt脚本记录扫描整个子光标链表。
--//调用kkshGetNextChild次数明显增加。

--//再来看看pdbname=book02p下的执行情况：
SCOTT@127.0.0.1:1521/book02p> @ aa5.txt 1
Session altered.
Session altered.
COUNT(NAME)
-----------
          1
Session altered.

# ./kkscsSearchChildList.bt 3364 verbose
Attached 14 probes

Tracing... Hit ^C to stop.

3364 oracle_3364_boo -> kkscsSearchChildList: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, flag=0x108, ...
3364 oracle_3364_boo    kkscsSearchChildList: Parent ready for search (kglkdadr=0x6b7c8ee0, kglhdpar=0x69399e98)
3364 oracle_3364_boo -> kpdbNameHash: 0x7e83abc4, 0x7 BOOK02P
3364 oracle_3364_boo -> kpdbNameHash: 0x7e83abc4, 0x7 BOOK02P
3364 oracle_3364_boo <- kpdbNameHash: 0xccac0de2 (3433827810)
3364 oracle_3364_boo <- kpdbNameHash: 0xccac0de2 (3433827810)
3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x6930bd80[0x69399068,0x6930b620]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x6930b620
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0, cno=0
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x6930bd30, cno=9
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x6930b5d0, cno=8
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkscsCheckCursor: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, 0x108, 0x0, 0x7ffdb0bac900, ...
3364 oracle_3364_boo    kkscsCheckCursor: next child is #9 (cs=0x6930bd30, pdbuid=1847639831)
3364 oracle_3364_boo <- kkscsCheckCursor: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x6930b620[0x6930bd80,0x693147e0]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x693147e0
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x6930bd30, cno=9
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x6930b5d0, cno=8
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69314790, cno=7
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x693147e0[0x6930b620,0x69314080]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x69314080
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x6930b5d0, cno=8
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x69314790, cno=7
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69314030, cno=6
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkscsCheckCursor: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, 0x108, 0x0, 0x7ffdb0bac900, ...
3364 oracle_3364_boo    kkscsCheckCursor: next child is #7 (cs=0x69314790, pdbuid=1847639831)
3364 oracle_3364_boo -> kkscsPruneChild: 0x7fb656e50060, xsc=0x7fb65353b7e0, cs=0x69314790, flg=0x7ffdb0bac980
3364 oracle_3364_boo    kkscsPruneChild: Child was pruned
3364 oracle_3364_boo <- kkscsPruneChild: ret=0x1 (1)
3364 oracle_3364_boo <- kkscsCheckCursor: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x69314080[0x693147e0,0x6931ce10]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x6931ce10
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x69314790, cno=7
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x69314030, cno=6
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x6931cdc0, cno=5
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x6931ce10[0x69314080,0x6931c6b0]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x6931c6b0
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x69314030, cno=6
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x6931cdc0, cno=5
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x6931c660, cno=4
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkscsCheckCursor: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, 0x108, 0x0, 0x7ffdb0bac900, ...
3364 oracle_3364_boo    kkscsCheckCursor: next child is #5 (cs=0x6931cdc0, pdbuid=1847639831)
3364 oracle_3364_boo -> kkscsPruneChild: 0x7fb656e50060, xsc=0x7fb65353b7e0, cs=0x6931cdc0, flg=0x7ffdb0bac980
3364 oracle_3364_boo    kkscsPruneChild: Child was pruned
3364 oracle_3364_boo <- kkscsPruneChild: ret=0x1 (1)
3364 oracle_3364_boo <- kkscsCheckCursor: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x6931c6b0[0x6931ce10,0x69326ef0]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x69326ef0
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x6931cdc0, cno=5
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x6931c660, cno=4
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69326ea0, cno=3
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x69326ef0[0x6931c6b0,0x69326790]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x69326790
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x6931c660, cno=4
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x69326ea0, cno=3
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69326740, cno=2
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkscsCheckCursor: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, 0x108, 0x0, 0x7ffdb0bac900, ...
3364 oracle_3364_boo    kkscsCheckCursor: next child is #3 (cs=0x69326ea0, pdbuid=1847639831)
3364 oracle_3364_boo -> kkscsPruneChild: 0x7fb656e50060, xsc=0x7fb65353b7e0, cs=0x69326ea0, flg=0x7ffdb0bac980
3364 oracle_3364_boo    kkscsPruneChild: Child was pruned
3364 oracle_3364_boo <- kkscsPruneChild: ret=0x1 (1)
3364 oracle_3364_boo <- kkscsCheckCursor: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x69326790[0x69326ef0,0x693997e0]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x693997e0
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x69326ea0, cno=3
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x69326740, cno=2
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69399790, cno=1
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkshGetNextChild: hsh=0xccac0de2, ht=0x69398fe8, cur_hbk=0x7ffdb0babcf8, cs=0x7ffdb0babd00, ...
3364 oracle_3364_boo    kkshGetNextChild: *ht=0x69399008 (pdbuid=1847639831)
3364 oracle_3364_boo    kkshGetNextChild: *cur_hbk=0x69399068
3364 oracle_3364_boo    kkshGetNextChild: cshsl=0x693997e0[0x69326790,0x693993d8]
3364 oracle_3364_boo    kkshGetNextChild: cshbk=0x693993d8
3364 oracle_3364_boo    kkshGetNextChild: prev: cs=0x69326740, cno=2
3364 oracle_3364_boo    kkshGetNextChild: curr: cs=0x69399790, cno=1
3364 oracle_3364_boo    kkshGetNextChild: next: cs=0x69399388, cno=0
3364 oracle_3364_boo <- kkshGetNextChild: ret=0x0 (0)

3364 oracle_3364_boo -> kkscsCheckCursor: 0x7fb656e50060, 0x7ffdb0bac980, xsc=0x7fb65353b7e0, 0x108, 0x0, 0x7ffdb0bac900, ...
3364 oracle_3364_boo    kkscsCheckCursor: next child is #1 (cs=0x69399790, pdbuid=1847639831)
3364 oracle_3364_boo -> kkscsPruneChild: 0x7fb656e50060, xsc=0x7fb65353b7e0, cs=0x69399790, flg=0x7ffdb0bac980
3364 oracle_3364_boo <- kkscsPruneChild: ret=0x0 (0)
3364 oracle_3364_boo <- kkscsCheckCursor: ret=0x68d8cf38 (1759039288)

3364 oracle_3364_boo    kkscsSearchChildList: complete: inspected 5 children in 219507 ns (219 us)
3364 oracle_3364_boo <- kkscsSearchChildList: ret=0x68d8cf38 (1759039288)
--//出现类似的情况。

--//也就是在pdbname hash冲突的情况下出现大量子光标的情况下调用kkshGetNextChild明显增加，应该尽量避免出现这种冲突，特别仅
--//仅少量使用pdb的情况下。
--//当然验证也很简单使用前面ora_hash1.txt脚本就可以验证。
--//关于kkscsSearchChildList.bt脚本可以在
--//https://www.0xchris.dev/posts/2026-06-08-child-cursor-scan-mechanics--an-investigation/找到相关下载链接。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3187398/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
下一篇： [20261102]光标跟踪事件9.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4014
  • 访问量
    7984454

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593