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

首页 > 数据库 > Oracle > [20260629]使用bpftrace跟踪library cache lock/library cache pin的疑问.txt

[20260629]使用bpftrace跟踪library cache lock/library cache pin的疑问.txt

原创 Oracle 作者：lfree 时间：2026-06-30 21:42:40 0 删除 编辑

[20260629]使用bpftrace跟踪library cache lock/library cache pin的疑问.txt

--//写了使用bpftrace跟踪library cache lock/library cache pin的脚本,想看看跟踪全部进程的情况，注：我的测试环境仅仅自己在
--//使用，如果如果生产系统，不知道会发生什么情况。

# cat  /home/oracle/study/202606/lkpn21c.bt
#!/usr/bin/env bpftrace
uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgllkal
/ $1 == 0 || pid == $1 /
{
    @lk++;
    printf("kgllkal count %02d -- handle address: %016lx, mode: %d ", @lk, arg2, arg3);
//  printf("spid %d ",pid);
    printf("kglnaobj address: %s\n", str(arg2 + 0x1c8, 256));
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kglpnal
/ $1 == 0 || pid == $1 /
{
    @pn++;
    printf("kglpnal count %02d -- handle address: %016lx, mode: %d ", @pn, arg2, arg3);
//  printf("spid %d ",pid);
    printf("kglnaobj address: %s\n", str(arg2 + 0x1c8, 256));
}

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.

2.测试:

# bpftrace /home/oracle/study/202606/lkpn21c.bt  0
Attached 2 probes
kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
--//仔细看基本成组出现。SCHEDULER_DISABLEDSYSCDB$ROOT,LAST_OBSERVED_EVENTSYSCDB$ROOT,CURRENT_OPEN_WINDOWSYSBOOK01P
kgllkal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kgllkal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kgllkal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kgllkal count 13 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 13 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 14 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 14 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 15 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 15 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kgllkal count 16 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 16 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 17 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kglpnal count 17 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 18 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
kglpnal count 18 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
Ckgllkal count 19 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kgllkal count 20 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
kgllkal count 21 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P

@lk: 21
@pn: 18
--//抓取到以上信息，而且非常密集.保存最后一列到文本。

$ sort aaa.txt | uniq -c
     13 CURRENT_OPEN_WINDOWSYSBOOK01P
     13 LAST_OBSERVED_EVENTSYSCDB$ROOT
     13 SCHEDULER_DISABLEDSYSCDB$ROOT

--//看看这些对应的对象是什么？
--//kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
SYS@book> @ sharepool/shp4z  0000000069cecf40 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address  0000000069CECF40 0000000069CECF40 SYS.SCHEDULER_DISABLED                            0          0          0 00               00                        0          0          0         0          0 1838314840                    30040          0

SYS@book> @ kglob 1838314840 '' ''
==============================
INST_ID                       : 1
OWNER                         : SYS
NAME                          : SCHEDULER_DISABLED
DB_LINK                       :
NAMESPACE                     : SCHEDULER GLOBAL ATTRIBUTE
TYPE                          : CURSOR
NAMESPACE_NUM                 : 51
NAMESPACE_HEX                 : 33
SHARABLE_MEM                  : 0
PERSISTENT_MEM                : 0
RUNTIME_MEM                   : 0
LOADS                         : 1
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 30040
INVALIDATIONS                 : 0
HASH_VALUE                    : 1838314840
HASH_HEX                      : 6d927558
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : UNKOWN
TIMESTAMP                     :
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 5750
PINNED_TOTAL                  : 5750
PROPERTY                      :
FULL_HASH_VALUE               : ba515653dca854868eb2f2e36d927558
CON_ID                        : 1
CON_NAME                      : CDB$ROOT
ADDR                          : 0000000069CECF40
PAR_ADDR                      : 0000000069CECF40
HANDLE_TYPE                   : parent handle address
KGLOBHD0                      : 00
KGLOBHD6                      : 00
EDITION                       :
SQL_ID                        :
KGL_BUCKET                    : 30040
OBJECT_STR                    : SCHEDULER_DISABLED.SYS.CDB$ROOT\x33\0\0\0
PL/SQL procedure successfully completed.

--//kgllkal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
SYS@book> @ sharepool/shp4z  000000007252da10 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                       KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ----------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address  000000007252DA10 000000007252DA10 SYS.LAST_OBSERVED_EVENT          0          0          0 000000007252D850 00                     4032          0          0      4032       4032 3239234648                    52312          0

SYS@book> @ kglob 3239234648 '' ''
==============================
INST_ID                       : 1
OWNER                         : SYS
NAME                          : LAST_OBSERVED_EVENT
DB_LINK                       :
NAMESPACE                     : SCHEDULER GLOBAL ATTRIBUTE
TYPE                          : SCHEDULER GLOBAL ATTRIBUTE
NAMESPACE_NUM                 : 51
NAMESPACE_HEX                 : 33
SHARABLE_MEM                  : 4032
PERSISTENT_MEM                : 0
RUNTIME_MEM                   : 0
LOADS                         : 1
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 52312
INVALIDATIONS                 : 0
HASH_VALUE                    : 3239234648
HASH_HEX                      : c112cc58
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : VALID
TIMESTAMP                     : 2021-07-27/19:28:54
PREVIOUS_TIMESTAMP            :
LOCKED_TOTAL                  : 6209
PINNED_TOTAL                  : 6209
PROPERTY                      :
FULL_HASH_VALUE               : a8c3acd3d915bdc2b1b9be4bc112cc58
CON_ID                        : 1
CON_NAME                      : CDB$ROOT
ADDR                          : 000000007252DA10
PAR_ADDR                      : 000000007252DA10
HANDLE_TYPE                   : parent handle address
KGLOBHD0                      : 000000007252D850
KGLOBHD6                      : 00
EDITION                       :
SQL_ID                        :
KGL_BUCKET                    : 52312
OBJECT_STR                    : LAST_OBSERVED_EVENT.SYS.CDB$ROOT\x33\0\0\0
PL/SQL procedure successfully completed.

--//kgllkal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
SYS@book> @ sharepool/shp4z  0000000069c53760 -1
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                       KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLHDBID   KGLOBT09
---------------------- ---------------- ---------------- ----------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- ----------
parent handle address  0000000069C53760 0000000069C53760 SYS.CURRENT_OPEN_WINDOW          0          0          0 0000000069C535A0 00                     4064          0          0      4064       4064  779486948                     1764          0

SYS@book> @ kglob 779486948 '' ''
==============================
INST_ID                       : 1
OWNER                         : SYS
NAME                          : CURRENT_OPEN_WINDOW
DB_LINK                       :
NAMESPACE                     : SCHEDULER GLOBAL ATTRIBUTE
TYPE                          : SCHEDULER GLOBAL ATTRIBUTE
NAMESPACE_NUM                 : 51
NAMESPACE_HEX                 : 33
SHARABLE_MEM                  : 4064
PERSISTENT_MEM                : 0
RUNTIME_MEM                   : 0
LOADS                         : 2
EXECUTIONS                    : 0
LOCKS                         : 0
PINS                          : 0
KEPT                          : NO
CHILD_LATCH                   : 1764
INVALIDATIONS                 : 0
HASH_VALUE                    : 779486948
HASH_HEX                      : 2e7606e4
LOCK_MODE                     : NONE
PIN_MODE                      : NONE
STATUS                        : VALID
TIMESTAMP                     : 2026-06-29/09:01:25
PREVIOUS_TIMESTAMP            : 2021-07-27/19:58:43
LOCKED_TOTAL                  : 7073
PINNED_TOTAL                  : 7073
PROPERTY                      :
FULL_HASH_VALUE               : 69cec2223cdef6313dff67e72e7606e4
CON_ID                        : 3
CON_NAME                      : BOOK01P
ADDR                          : 0000000069C53760
PAR_ADDR                      : 0000000069C53760
HANDLE_TYPE                   : parent handle address
KGLOBHD0                      : 0000000069C535A0
KGLOBHD6                      : 00
EDITION                       :
SQL_ID                        :
KGL_BUCKET                    : 1764
OBJECT_STR                    : CURRENT_OPEN_WINDOW.SYS.BOOK01P\x33\0\0\0
PL/SQL procedure successfully completed.

--//基本都是NAMESPACE = SCHEDULER GLOBAL ATTRIBUTE的对象，估计是后果后台进程执行，应该是调度进程。
--//验证FULL_HASH_VALUE是否能对上。
$ sql_idz.sh 'CURRENT_OPEN_WINDOW.SYS.BOOK01P\x33\0\0\0' 3
sql_text = CURRENT_OPEN_WINDOW.SYS.BOOK01P\x33\0\0\0
full_hash_value(16) = 69CEC2223CDEF6313DFF67E72E7606E4 or 69cec2223cdef6313dff67e72e7606e4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--//完全对上。
xxxxx_matching_signature(10) = 4467403597989414628 or  22914147671698966244
hash_value(10) = 779486948 or hash_value(16) = 2E7606E4 or 2e7606e4
kgl_backet(10) = 1764 or kgl_bucket(16) =  6E4 or 6e4
sql_id(16) = 3DFF67E72E7606E4 or 3dff67e72e7606e4
sql_id(32) = 3vzv7wwr7c1r4
sql_id(32) = 3vzv7wwr7c1r4
sql_id(32) = 3vzv7wwr7c1r4

--//取消注解printf("spid %d ",pid)：
# bpftrace /home/oracle/study/202606/lkpn21c.bt  0
Attached 2 probes
kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
kglpnal count 01 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
...

--//spid=3132进程引起的。
# ps -fp 3132
UID          PID    PPID  C STIME TTY          TIME CMD
oracle      3132       1  0 09:01 ?        00:00:06 ora_cjq0_book

# bpftrace /home/oracle/study/202606/lkpn21c.bt  3132 | ts.awk
Attached 2 probes
[2026-06-29 09:44:00] kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:00] kglpnal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:00] kgllkal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:00] kglpnal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:00] kgllkal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:00] kglpnal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:05] kgllkal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:05] kglpnal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:05] kgllkal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:05] kglpnal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:05] kgllkal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:05] kglpnal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:10] kgllkal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:10] kglpnal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:10] kgllkal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:10] kglpnal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:10] kgllkal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:10] kglpnal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:15] kgllkal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:15] kglpnal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:44:15] kgllkal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:15] kglpnal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:44:15] kgllkal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:44:15] kglpnal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
--//可以发现ora_cjq0_book进程是每5秒一个循环在探测。

--//有点意外的是有时候会出现密集的输出:
# bpftrace /home/oracle/study/202606/lkpn21c.bt  3132 | ts.awk
Attached 2 probes
[2026-06-29 09:55:29] kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:29] kglpnal count 01 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:29] kgllkal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:29] kglpnal count 02 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:29] kgllkal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:29] kglpnal count 03 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:29] kgllkal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:29] kglpnal count 04 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:29] kgllkal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:29] kglpnal count 05 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:29] kgllkal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:29] kglpnal count 06 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:30] kgllkal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kglpnal count 07 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kgllkal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:30] kglpnal count 08 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:30] kgllkal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:30] kglpnal count 09 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:30] kgllkal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kglpnal count 10 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kgllkal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:30] kglpnal count 11 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 09:55:30] kgllkal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:30] kglpnal count 12 -- handle address: 0000000069c53760, mode: 2 kglnaobj address: CURRENT_OPEN_WINDOWSYSBOOK01P
[2026-06-29 09:55:30] kgllkal count 13 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kglpnal count 13 -- handle address: 0000000069cecf40, mode: 2 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 09:55:30] kgllkal count 14 -- handle address: 000000007252da10, mode: 2 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT

# bpftrace /home/oracle/study/202606/lkpn21c.bt  3132|ts.awk
Attached 2 probes
[2026-06-29 10:03:40] kgllkal count 01 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:40] kglpnal count 01 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:40] kgllkal count 02 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:40] kglpnal count 02 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:45] kgllkal count 03 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:45] kglpnal count 03 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:45] kgllkal count 04 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:45] kglpnal count 04 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:50] kgllkal count 05 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:50] kglpnal count 05 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:50] kgllkal count 06 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:50] kglpnal count 06 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:55] kgllkal count 07 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:55] kglpnal count 07 -- handle address: 0000000069cecf40, mode: 2 spid 3132 kglnaobj address: SCHEDULER_DISABLEDSYSCDB$ROOT
[2026-06-29 10:03:55] kgllkal count 08 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
[2026-06-29 10:03:55] kglpnal count 08 -- handle address: 000000007252da10, mode: 2 spid 3132 kglnaobj address: LAST_OBSERVED_EVENTSYSCDB$ROOT
--//也会遇到没有CURRENT_OPEN_WINDOWSYSBOOK01P的情况.
--//因为仅仅1个进程使用相关对象,即使密集执行也不会出现阻塞的情况.

5.附上测试使用脚本:

$ cat shp4z.sql

column N0_6_16 format 99999999
column fcura_addrlen new_value _fcura_addrlen  format 999
column handle_type format a22

set termout off
select vsize(addr)*2 fcura_addrlen from x$dual;
set termout on

SELECT DECODE (kglhdadr,
               kglhdpar, 'parent handle address',
               'child handle address')
       handle_type,
       kglhdadr,
       kglhdpar,
       --//substr(kglnaobj,1,40) c40,
       substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,
       KGLHDLMD,
       KGLHDPMD,
       kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
       kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
       kglnahsh,
       kglobt03,
       kglhdbid,
       kglobt09
  FROM x$kglob
 WHERE
    KGLHDPAR = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLHDADR = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBHD0 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD1 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD2 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD3 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD4 = lpad(upper('&1'), &_fcura_addrlen, '0')
--or  KGLOBHD5 = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBHD6 = lpad(upper('&1'), &_fcura_addrlen, '0')
or  KGLOBT03 = lower('&1')
or  KGLNAHSH= &2;

$ cat kglob12c.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   kglob.sql
-- Purpose:     query v$db_object_cache view
--
-- Author:      lfree
--
-- Usage:
--     @ kglob <sql_id> <hash_value> [addr] <where_condition>
-- for example
--     @ kglob 0 123456678
--     @ kglob 7h35uxf5uhmm1 0 ''
--     @ kglob ''  0 '' KGLHDBID=121212
--
--------------------------------------------------------------------------------
set term off head off
--define noprint='noprint'
--define _tpt_comment=''

define _tpt_offset=48

col tpt_version_old  new_value _tpt_version_old
col tpt_version_new  new_value _tpt_version_new
col tpt_noprint      new_value _tpt_noprint
col tpt_comment      new_value _tpt_comment

/*
WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 10  THEN '' ELSE '--' END tpt_version_new
            FROM version;
*/

column fcura_addrlen new_value _fcura_addrlen format 999
select vsize(addr)*2 fcura_addrlen from x$dual;

define tt='&&1'
@ hd &&2
define 2=&&v_n10
define 1='&&tt'

col 3 new_value 3
col v_addr  new_value _v_addr
select null "3",null v_addr  from dual where 1=2;

select decode('&3',null,'--','') tpt_comment from dual;

select
&_tpt_comment to_char(to_number('&3',lpad('x',&_fcura_addrlen,'x'))+&_tpt_offset,'fm'||lpad('x',&_fcura_addrlen,'x')) v_addr,
'x'
from dual;

col 4 new_value 4
select null "4" from dual where 1=0;
select decode((length(replace('&4',' '))),null,'0=1',1,'0=1','&4') "4" from dual;

select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
 inst_id                                                                                                                           INST_ID
,kglnaown                                                                                                                          OWNER
,kglnaobj                                                                                                                          NAME
,kglnadlk                                                                                                                          DB_LINK
,kglhdnsd                                                                                                                          NAMESPACE
,kglobtyd                                                                                                                          TYPE
,kglhdnsp                                                                                                                          NAMESPACE_NUM
,to_char(kglhdnsp,'FMxx')                                                                                                          NAMESPACE_HEX   --
,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6                                                                    SHARABLE_MEM
,kglobt08 + kglobt11                                                                                                               PERSISTENT_MEM  --
,kglobt10                                                                                                                          RUNTIME_MEM     --
,kglhdldc                                                                                                                          LOADS
,kglhdexc                                                                                                                          EXECUTIONS
,kglhdlkc                                                                                                                          LOCKS
,kglobpc0                                                                                                                          PINS
,decode(kglhdkmk,0,'NO','YES')                                                                                                     KEPT
,kglhdclt                                                                                                                          CHILD_LATCH
,kglhdivc                                                                                                                          INVALIDATIONS
,kglnahsh                                                                                                                          HASH_VALUE
,to_char(kglnahsh,'fmxxxxxxxx')                                                                                                    HASH_HEX        --
,decode(kglhdlmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         LOCK_MODE
,decode(kglhdpmd,0, 'NONE',1, 'NULL', 2, 'SHARED',3, 'EXCLUSIVE','UNKOWN')                                                         PIN_MODE
,decode(kglobsta,1, 'VALID',2,'VALID_AUTH_ERROR',3,'VALID_COMPILE_ERROR',4,'VALID_UNAUTH',5,'INVALID_UNAUTH',6,'INVALID','UNKOWN') STATUS
,substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            TIMESTAMP
,substr(to_char(kglnaptm,'YYYY-MM-DD/HH24:MI:SS'),1,19)                                                                            PREVIOUS_TIMESTAMP
,kgloblct                                                                                                                          LOCKED_TOTAL
,kglobpct                                                                                                                          PINNED_TOTAL
,kglobprop                                                                                                                         PROPERTY
,kglnahsv                                                                                                                          FULL_HASH_VALUE
,con_id                                                                                                                            CON_ID
,kglnacon                                                                                                                          CON_NAME
,kglhdadr                                                                                                                          ADDR        --
,kglhdpar                                                                                                                          PAR_ADDR    --
,decode (kglhdadr, kglhdpar, 'parent handle address', 'child handle address')                                                      HANDLE_TYPE --
,kglobhd0                                                                                                                          KGLOBHD0    --
,kglobhd6                                                                                                                          KGLOBHD6    --
,kglnaedn                                                                                                                          EDITION
,kglobt03                                                                                                                          SQL_ID      --
,kglhdbid                                                                                                                          KGL_BUCKET  --
--&&_tpt_comment,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60)  OBJECT_STR
,substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown||'.'||kglnacon||'\x'||to_char(kglhdnsp,'FMxx')||'\0\0\0'), '(name not found)'),chr(13),'') ,1,60) OBJECT_STR
from x$kglob
where kglnaobj is not null and (KGLOBT03 = lower('&1') or  KGLNAHSH= &2
--&&_tpt_comment or to_char(KGLNAHSH,'fm0xxxxxxx')=lower(lpad('&3',8,0))
&&_tpt_comment or  KGLHDPAR = lpad(upper('&3'), &_fcura_addrlen, '0')
&&_tpt_comment or  KGLHDADR = lpad(upper('&3'), &_fcura_addrlen, '0')
&&_tpt_comment or  KGLOBHD0 = lpad(upper('&3'), &_fcura_addrlen, '0')
&&_tpt_comment or  KGLOBHD6 = lpad(upper('&3'), &_fcura_addrlen, '0')
&&_tpt_comment or  KGLHDPAR = lpad(upper('&&_v_addr'), &_fcura_addrlen, '0')
&&_tpt_comment or  KGLHDADR = lpad(upper('&&_v_addr'), &_fcura_addrlen, '0')
or (&&4)
);
set term on head on
@ pr
define _tpt_comment='--'
define 3=''
define 4=''

$ cat hd.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name:        hd.sql
-- Purpose:     display hex or dec to hex or dec
--
-- Author:      lfree
-- Usage:
--     @ hd <hex|dec>
--
--------------------------------------------------------------------------------

define vc=&1

column text format a20
column hex_status format a10 new_value v_hex_status
column n10 format 9999999999999999 new_value v_n10
column c16 format a32 new_value v_c16
column u16 format a32 new_value v_u16

SELECT text
      ,n10
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,upper(REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ')) u16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));

# cat /usr/local/bin/ts.awk
# /bin/bash
gawk '{ print strftime("[%Y-%m-%d %H:%M:%S]"), $0 }'

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

0
0
上一篇： [20260629]跟踪library cache lock library cache pin使用bpftrace.txt
下一篇： [20260629]跟踪ora_cjq0_进程.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4014
  • 访问量
    7990819

最新文章

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

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

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

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