[20250727]关于访问系统视图v$sqlXXXX的问题.txt
–//如果定位解决问题，经常会访问系统视图以v$sql开头的视图。例如v$sql视图,其底层视图是x$kglcursor_child,v$sqlarea其底层视
–//图是x$kglcursor_child_sqlid.如果几个会话同时访问,以前测试过会出现library cache: mutex X等待事件,当时许多概念不是很清
–//晰，里面一些细节很没有做探究，有时间重复测试看看。
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.
2.测试脚本建立：
$ cat loop.txt
declare
v_count number;
begin
for i in 1 .. &&1 loop
–select count(*) into v_count from v$session ;
–select count(*) into v_count from v$sql ;
select count(*) into v_count from v$sql where rownum<=1;
end loop;
end ;
/
–//仅仅取1条记录。
$ cat record.txt
set verify off
variable v_method varchar2(20)
exec :v_method := (case when ‘&2’ >= ‘1’ and ‘&2′<=’9999’ then ‘QQQQ’ else ‘&2’ end);
define t=&&1;
set term off
column 3 new_value 3;
select decode(‘&3′,null,’loop.txt’,’&&3′) “3” from dual;
set term on
@@ &&3 5 &&2
–//insert into job_times values ( sys_context (‘userenv’, ‘sid’) ,dbms_utility.get_time ,:v_method) ;
–//commit ;
@@ &&3 &&t &&2
–//update job_times set time_ela = dbms_utility.get_time – time_ela where sid=sys_context (‘userenv’, ‘sid’) and method= :v_method;
–//commit;
quit
3.测试：
–//测试前执行：
SYS@book> select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum<=5;
SQL_ID HASH_VALUE MOD(HASH_VALUE,POWER(2,17)) FIRST_LOAD_TIME CON_ID
————- ———- ————————— ————————————– ———-
94qn6y14kw01g 1227751471 47 2025-07-27/09:58:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
SYS@book01p> select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum<=5;
SQL_ID HASH_VALUE MOD(HASH_VALUE,POWER(2,17)) FIRST_LOAD_TIME CON_ID
————- ———- ————————— ————————————– ———-
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
06gfrprr7w0r2 4001235682 738 2025-07-27/09:47:19 3
06gfrprr7w0r2 4001235682 738 2025-07-27/09:47:19 3
–//注意两者的区别，前者在cdb层面上访问，后者在pdb层面上访问。
$ zzdate ; seq 50 | xargs -P 50 -IQ sqlplus scott/book@book01p @record.txt 2e4 aaaa loop.txt > /dev/null; zzdate
trunc(sysdate)+10/24+12/1440+36/86400 1753582356.828121789
trunc(sysdate)+10/24+13/1440+05/86400 1753582385.728571377
SYS@book> @ ashtop event,sql_id 1=1 trunc(sysdate)+10/24+12/1440+36/86400 trunc(sysdate)+10/24+13/1440+05/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- —————————————— ————- ——————- ——————- ———- ——– ———–
1043 36.0 85% | library cache: bucket mutex X 6s0qhxyxjpurx 2025-07-27 10:12:38 2025-07-27 10:13:04 1043 27 1043
129 4.4 10% | 6s0qhxyxjpurx 2025-07-27 10:12:37 2025-07-27 10:13:04 126 24 129
29 1.0 2% | library cache: mutex X 6s0qhxyxjpurx 2025-07-27 10:12:37 2025-07-27 10:13:02 29 16 29
12 .4 1% | cursor: pin S 2025-07-27 10:12:39 2025-07-27 10:12:49 1 3 3
9 .3 1% | fm1tb5mss6fsj 2025-07-27 10:12:40 2025-07-27 10:12:54 8 7 8
6 .2 0% | 2025-07-27 10:12:38 2025-07-27 10:12:57 1 5 5
3 .1 0% | cursor: mutex S 6s0qhxyxjpurx 2025-07-27 10:12:40 2025-07-27 10:12:58 3 3 3
1 .0 0% | latch: active service list 2025-07-27 10:13:02 2025-07-27 10:13:02 1 1 1
1 .0 0% | log file sync 2025-07-27 10:12:38 2025-07-27 10:12:38 1 1 1
9 rows selected.
SYS@book> @ sql_id 6s0qhxyxjpurx
— SQL_ID = 6s0qhxyxjpurx come from shared pool
SELECT COUNT(*) FROM V$SQL WHERE ROWNUM<=1;
–//实际上大部分集中在library cache: bucket mutex X，以前的测试标识为library cache: mutex X。现在版本标识为library
–//cache: bucket mutex X.当然仅仅密集的执行会出现这样的情况。
SYS@book> @ ashtop event,p1raw,p1,p3raw 1=1 trunc(sysdate)+10/24+12/1440+36/86400 trunc(sysdate)+10/24+13/1440+05/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- —————————————— —————– ———- —————– ——————- ——————- ———- ——– ———–
241 8.3 20% | library cache: bucket mutex X 00000000000000C0 192 0000000000000031 2025-07-27 10:12:39 2025-07-27 10:13:02 241 15 241
115 4.0 9% | library cache: bucket mutex X 000000000000004A 74 0000000000000031 2025-07-27 10:12:39 2025-07-27 10:13:04 115 12 115
104 3.6 8% | library cache: bucket mutex X 0000000000000074 116 0000000000000031 2025-07-27 10:12:39 2025-07-27 10:13:04 104 10 104
93 3.2 8% | library cache: bucket mutex X 0000000000000033 51 0000000000000031 2025-07-27 10:12:38 2025-07-27 10:12:57 93 6 93
68 2.3 6% | library cache: bucket mutex X 000000000000002F 47 0000000000000031 2025-07-27 10:12:38 2025-07-27 10:13:03 68 6 68
65 2.2 5% | library cache: bucket mutex X 000000000000008D 141 0000000000000031 2025-07-27 10:12:44 2025-07-27 10:13:02 65 6 65
64 2.2 5% | library cache: bucket mutex X 0000000000000064 100 0000000000000031 2025-07-27 10:12:40 2025-07-27 10:13:00 64 4 64
49 1.7 4% | library cache: bucket mutex X 00000000000000C0 192 0000000000000091 2025-07-27 10:12:39 2025-07-27 10:13:03 49 17 49
33 1.1 3% | 192 2025-07-27 10:12:39 2025-07-27 10:13:02 32 12 33
27 .9 2% | library cache: bucket mutex X 0000000000000011 17 0000000000000031 2025-07-27 10:12:39 2025-07-27 10:12:57 27 6 27
26 .9 2% | library cache: bucket mutex X 000000000000002A 42 0000000000000031 2025-07-27 10:12:45 2025-07-27 10:13:03 26 4 26
23 .8 2% | library cache: bucket mutex X 0000000000000005 5 0000000000000031 2025-07-27 10:12:39 2025-07-27 10:13:02 23 6 23
21 .7 2% | library cache: bucket mutex X 000000000000003C 60 0000000000000031 2025-07-27 10:12:47 2025-07-27 10:13:01 21 3 21
21 .7 2% | library cache: bucket mutex X 00000000000000A2 162 0000000000000031 2025-07-27 10:12:49 2025-07-27 10:13:04 21 3 21
21 .7 2% | library cache: bucket mutex X 00000000000000B9 185 0000000000000031 2025-07-27 10:12:40 2025-07-27 10:12:58 21 3 21
19 .7 2% | library cache: bucket mutex X 0000000000000037 55 0000000000000031 2025-07-27 10:12:44 2025-07-27 10:13:04 19 4 19
19 .7 2% | library cache: bucket mutex X 000000000000007E 126 0000000000000031 2025-07-27 10:12:45 2025-07-27 10:12:49 19 2 19
19 .7 2% | library cache: mutex X 00000000D19000C0 3515875520 D19000C00000007C 2025-07-27 10:12:37 2025-07-27 10:13:02 19 12 19
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
17 .6 1% | library cache: bucket mutex X 00000000000000B1 177 0000000000000031 2025-07-27 10:12:44 2025-07-27 10:12:45 17 2 17
16 .6 1% | 51 2025-07-27 10:12:40 2025-07-27 10:12:57 15 6 16
12 .4 1% | cursor: pin S 00000000BB1AEAFD 3139103485 0000000900000000 2025-07-27 10:12:39 2025-07-27 10:12:49 1 3 3
11 .4 1% | library cache: bucket mutex X 00000000000000B4 180 0000000000000031 2025-07-27 10:12:40 2025-07-27 10:13:03 11 3 11
10 .3 1% | library cache: bucket mutex X 000000000000004A 74 0000000000000091 2025-07-27 10:12:46 2025-07-27 10:13:04 10 6 10
10 .3 1% | 5 2025-07-27 10:12:44 2025-07-27 10:13:02 10 4 10
10 .3 1% | 3515875520 2025-07-27 10:12:44 2025-07-27 10:13:03 10 8 10
8 .3 1% | 74 2025-07-27 10:12:44 2025-07-27 10:13:04 8 5 8
7 .2 1% | library cache: mutex X 00000000D19000C0 3515875520 D19000C00000007B 2025-07-27 10:12:42 2025-07-27 10:13:00 7 7 7
7 .2 1% | 141 2025-07-27 10:12:44 2025-07-27 10:13:02 7 4 7
6 .2 0% | 116 2025-07-27 10:12:48 2025-07-27 10:13:04 6 5 6
5 .2 0% | library cache: bucket mutex X 000000000000002F 47 0000000000000091 2025-07-27 10:12:38 2025-07-27 10:13:03 5 3 5
30 rows selected.
–//如果仔细看library cache: bucket mutex X等待事件的P1，发现都不会大于2^17 = 131072.P3RAW=0x31=49.0×91 = 145.
–//注：以前的测试已经说明小于2^17也可能是sql语句的hash值，不过大部分情况应该是library cache bucket。
–//你可以结合P3的输出。比如下划线P3RAW=D19000C00000007B，前8位D19000C0 = 3515875520，正好等于P1，确定是否是sql语句或者
–//其他对象的hash值。
SYS@book> @ ashtop p1 “event=’library cache: bucket mutex X’ ” trunc(sysdate)+10/24+12/1440+36/86400 trunc(sysdate)+10/24+13/1440+05/86400
Total Distinct Distinct Distinct
Seconds AAS %This P1 FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- ———- ——————- ——————- ———- ——– ———–
290 10.0 28% | 192 2025-07-27 10:12:39 2025-07-27 10:13:03 290 18 290
125 4.3 12% | 74 2025-07-27 10:12:39 2025-07-27 10:13:04 125 13 125
105 3.6 10% | 116 2025-07-27 10:12:39 2025-07-27 10:13:04 105 11 105
98 3.4 9% | 51 2025-07-27 10:12:38 2025-07-27 10:12:57 98 6 98
73 2.5 7% | 47 2025-07-27 10:12:38 2025-07-27 10:13:03 73 6 73
68 2.3 7% | 141 2025-07-27 10:12:44 2025-07-27 10:13:02 68 6 68
64 2.2 6% | 100 2025-07-27 10:12:40 2025-07-27 10:13:00 64 4 64
27 .9 3% | 17 2025-07-27 10:12:39 2025-07-27 10:12:57 27 6 27
26 .9 2% | 42 2025-07-27 10:12:45 2025-07-27 10:13:03 26 4 26
25 .9 2% | 5 2025-07-27 10:12:38 2025-07-27 10:13:02 25 8 25
21 .7 2% | 60 2025-07-27 10:12:47 2025-07-27 10:13:01 21 3 21
21 .7 2% | 162 2025-07-27 10:12:49 2025-07-27 10:13:04 21 3 21
21 .7 2% | 185 2025-07-27 10:12:40 2025-07-27 10:12:58 21 3 21
19 .7 2% | 55 2025-07-27 10:12:44 2025-07-27 10:13:04 19 4 19
19 .7 2% | 126 2025-07-27 10:12:45 2025-07-27 10:12:49 19 2 19
18 .6 2% | 177 2025-07-27 10:12:44 2025-07-27 10:12:45 18 2 18
11 .4 1% | 180 2025-07-27 10:12:40 2025-07-27 10:13:03 11 3 11
5 .2 0% | 154 2025-07-27 10:12:39 2025-07-27 10:12:44 5 2 5
4 .1 0% | 67 2025-07-27 10:12:52 2025-07-27 10:12:52 4 1 4
2 .1 0% | 98 2025-07-27 10:12:44 2025-07-27 10:13:02 2 2 2
1 .0 0% | 39 2025-07-27 10:12:40 2025-07-27 10:12:40 1 1 1
21 rows selected.
–//还可以发现一个特点，就是p1的值都不是一样的，但是都没有大于192,而且P1=192的出现的最多。估计与我执行select count(*)
–//into v_count from v$sqlwhere rownum<=1;仅仅取1行记录有关。
–//测试后执行：
SYS@book01p> select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum<=5;
SQL_ID HASH_VALUE MOD(HASH_VALUE,POWER(2,17)) FIRST_LOAD_TIME CON_ID
————- ———- ————————— ————————————– ———-
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
06gfrprr7w0r2 4001235682 738 2025-07-27/09:47:19 3
06gfrprr7w0r2 4001235682 738 2025-07-27/09:47:19 3
SYS@book> select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum<=5;
SQL_ID HASH_VALUE MOD(HASH_VALUE,POWER(2,17)) FIRST_LOAD_TIME CON_ID
————- ———- ————————— ————————————– ———-
94qn6y14kw01g 1227751471 47 2025-07-27/09:58:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 1
gngtvs38t0060 3515875520 192 2025-07-27/09:47:24 3
–//是否可以猜测执行先从library cache bucket=0开始扫描。如果存在对象给对应的bucket加上mutex X。这样扫描到bucket=192时找
–//到符合条件的记录。
SYS@book> @ mutexprofz idn,hash,loc,maddr “ts>=trunc(sysdate)+10/24+12/1440+36/86400 and ts<=trunc(sysdate)+10/24+13/1440+05/86400 and idn<=192”
— MutexProf by Tanel Poder (http://www.tanelpoder.com)
— Showing profile of top 50 sleeps…
— column info : id idn hash hash_value=>hash_value ts=>sleep_timestamp
— req=>requesting_session blk=>blocking_session val=>mutex_value maddr=>mutex_addr
SUM_SLEEPS GETS_DIFF MUTEX_TYPE IDN HASH GET_LOCATION mutex_addr SQL_ID OBJECT_NAME
———- ————– ————— ———- ———- ——————————— ——————– ————- ———————————
63 361088 Library Cache 141 kglic1 49 0000000075BFB3F8 (name not found)
57 239614 Library Cache 192 kglic1 49 0000000075BFBD88 (name not found)
53 446588 Library Cache 5 kglic1 49 0000000075BF9A78 (name not found)
49 213570 Library Cache 126 kglic1 49 0000000075BFB128 (name not found)
47 431028 Library Cache 60 kglic1 49 0000000075BFA4C8 (name not found)
41 264890 Library Cache 51 kglic1 49 0000000075BFA318 (name not found)
37 235694 Library Cache 162 kglic1 49 0000000075BFB7E8 (name not found)
31 258024 Library Cache 185 kglic1 49 0000000075BFBC38 (name not found)
23 177626 Library Cache 67 kglic1 49 0000000075BFA618 (name not found)
23 328917 Library Cache 74 kglic1 49 0000000075BFA768 (name not found)
23 248323 Library Cache 55 kglic1 49 0000000075BFA3D8 (name not found)
23 89019 Library Cache 17 kglic1 49 0000000075BF9CB8 (name not found)
22 239897 Library Cache 47 kglic1 49 0000000075BFA258 (name not found)
21 286103 Library Cache 116 kglic1 49 0000000075BFAF48 (name not found)
19 1425357 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 0000000067B26FB0 (name not found)
19 272936 Library Cache 113 kglic1 49 0000000075BFAEB8 (name not found)
18 806504 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 0000000066B348A0 (name not found)
18 132440 Library Cache 39 kglic1 49 0000000075BFA0D8 (name not found)
17 177166 Library Cache 154 kglic1 49 0000000075BFB668 (name not found)
16 35594 Library Cache 177 kglic1 49 0000000075BFBAB8 (name not found)
16 86109 Library Cache 192 kglic4 145 0000000075BFBD88 (name not found)
14 912888 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 000000006D338FC0 (name not found)
12 1770707 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 000000006D4CB2B0 (name not found)
11 923440 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 0000000067149170 (name not found)
7 260783 Library Cache 98 kglic1 49 0000000075BFABE8 (name not found)
7 4617 Library Cache 74 kglic4 145 0000000075BFA768 (name not found)
6 737452 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 0000000069A148D8 (name not found)
5 9378 Library Cache 100 kglic1 49 0000000075BFAC48 (name not found)
4 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 000000006BA90530 (name not found)
4 154600 Library Cache 180 kglic1 49 0000000075BFBB48 (name not found)
3 76171 Library Cache 47 kglic4 145 0000000075BFA258 (name not found)
3 Library Cache 51 kglic4 145 0000000075BFA318 (name not found)
3 426969 Cursor Stat 0 qesdpIsResolved [KKSSTALOC14] 0000000071314130 (name not found)
2 106936 Library Cache 141 kglic4 145 0000000075BFB3F8 (name not found)
1 Library Cache 5 kglic4 145 0000000075BF9A78 (name not found)
1 Library Cache 42 kglic1 49 0000000075BFA168 (name not found)
36 rows selected.
–//GET_LOCATION里面的数字49(0x31),145(0x91)对应p3raw的后8位。
4.继续测试：
–//前面P1=192,47上好理解，有对象在相应的buckect，出现其他数字，又是怎么的情况呢？
–//注：我反复关闭数据库重复测试多次，发现出现的数字还是这些，基本没有变化。
–//转储library_cache：
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump library_cache 4
Statement processed.
$ grep “^Bucket” /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4197.trc |head -22
Bucket: #=5 Mutex=0x75bf9a78(1198295875584, 3000888, 6134, 6)
Bucket: #=17 Mutex=0x75bf9cb8(1198295875584, 2000597, 2379, 6)
Bucket: #=39 Mutex=0x75bfa0d8(1198295875584, 2000578, 3474, 6)
Bucket: #=42 Mutex=0x75bfa168(1198295875584, 2000562, 3569, 6)
Bucket: #=47 Mutex=0x75bfa258(1198295875584, 2000588, 8769, 6)
Bucket: #=51 Mutex=0x75bfa318(1198295875584, 2000600, 7764, 6)
Bucket: #=55 Mutex=0x75bfa3d8(1198295875584, 2000583, 4032, 6)
Bucket: #=60 Mutex=0x75bfa4c8(1198295875584, 2000602, 3026, 6)
Bucket: #=67 Mutex=0x75bfa618(1198295875584, 2000575, 3334, 6)
Bucket: #=74 Mutex=0x75bfa768(1198295875584, 3000880, 18732, 6)
Bucket: #=98 Mutex=0x75bfabe8(1198295875584, 2000584, 2588, 6)
Bucket: #=100 Mutex=0x75bfac48(1198295875584, 2000564, 3009, 6)
Bucket: #=113 Mutex=0x75bfaeb8(1198295875584, 2000601, 4520, 6)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Bucket: #=116 Mutex=0x75bfaf48(1198295875584, 3000865, 8147, 6)
Bucket: #=123 Mutex=0x75bfb098(1198295875584, 2, 0, 6)
Bucket: #=126 Mutex=0x75bfb128(1198295875584, 2000622, 3340, 6)
Bucket: #=141 Mutex=0x75bfb3f8(1198295875584, 3000935, 11689, 6)
Bucket: #=154 Mutex=0x75bfb668(1198295875584, 2000579, 2747, 6)
Bucket: #=162 Mutex=0x75bfb7e8(1198295875584, 2000598, 2975, 6)
Bucket: #=180 Mutex=0x75bfbb48(1198295875584, 2000570, 3487, 6)
Bucket: #=192 Mutex=0x75bfbd88(1198295875584, 2000715, 53345, 6)
Bucket: #=195 Mutex=0x75bfbe18(1198295875584, 31, 0, 6)
–//转储的对象第2个值表示gets的数量，第3个值表示sleeps的数量。可以发现在Bucket:#=192之前的仅仅有1个很小(Bucket: #=123)，
–//应该是测试会产生的对象。
–//单独保存前面的P1值到文件h1.txt.
$ cat h1.txt | sort -n | paste -sd” ”
5 17 39 42 47 51 55 60 67 74 98 100 116 126 141 154 162 177 180 185 192
–//仅仅113,123没有出现在h1.txt文件(123应该是后增加的，gets=2)，而h1.txt 记录的177，180 的bucket没有对象，估计已经刷出共享池。
–//确实这样，Bucket: #=5对象是一条sql语句(sql_id=0f212fup8n005)，已经不存在了。
SYS@book> @ sharepool/shp4 0f212fup8n005 -1
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
———————- —————- —————- —————————————- ———- ———- ———- —————- —————- ———- ———- ———- ——— ———- ———- ————- ———-
parent handle address 000000006C307AA0 000000006C307AA0 SELECT /*+ OPT_PARAM 0 0 0 00 00 0 0 0 0 0 2861170693 0f212fup8n005 65535
–//仅仅父游标句柄还在，其他都没有了。*/
$ egrep “^Bucket|^ ObjectName” /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4197.trc |head -48
Bucket: #=5 Mutex=0x75bf9a78(1198295875584, 3000888, 6134, 6)
ObjectName: Name=SELECT /*+ OPT_PARAM(‘_parallel_syspls_obey_force’ ‘false’) */ S.SNAME, S.PNAME, S.PVAL1, S.PVAL2 FROM WRI$_OPTSTAT_AUX_HISTORY S WHERE S.SAVTIME = (SELECT /*+ no_unnest */ MIN(SB.SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY SB WHERE SB.SAVTIME > :B1 )
Bucket: #=17 Mutex=0x75bf9cb8(1198295875584, 2000597, 2379, 6)
ObjectName: Name=CDB$ROOT.SYS.WRI$_ADV_COMPRESSION_T
Bucket: #=39 Mutex=0x75bfa0d8(1198295875584, 2000578, 3474, 6)
ObjectName: Name=SELECT /*+ ordered index(u) index(o) index(po) OPT_PARAM(‘_parallel_syspls_obey_force’ ‘false’) */
Bucket: #=42 Mutex=0x75bfa168(1198295875584, 2000562, 3569, 6)
ObjectName: Name=CDB$ROOT.SYS.AWR_ROOT_RULE_SET
Bucket: #=47 Mutex=0x75bfa258(1198295875584, 2000588, 8769, 6)
ObjectName: Name=SELECT NVL(TO_NUMBER(EXTRACT(XMLTYPE(:B2 ), :B1 )), 0) FROM DUAL
Bucket: #=51 Mutex=0x75bfa318(1198295875584, 2000600, 7764, 6)
ObjectName: Name=LOCK TABLE “WRH$_LATCH” PARTITION (“WRH$_LATCH_1073777561_MXSN”) IN EXCLUSIVE MODE NOWAIT
Bucket: #=55 Mutex=0x75bfa3d8(1198295875584, 2000583, 4032, 6)
ObjectName: Name=CDB$ROOT.SYS.WRH$_WAITSTAT_PK
Bucket: #=60 Mutex=0x75bfa4c8(1198295875584, 2000602, 3026, 6)
ObjectName: Name=CDB$ROOT.PUBLIC.AWR_CDB_CHANNEL_WAITS
Bucket: #=67 Mutex=0x75bfa618(1198295875584, 2000575, 3334, 6)
ObjectName: Name=BOOK01P.RTSCTX5e201d1b30d15a74805baf03f9c1ce6d Child:1
Bucket: #=74 Mutex=0x75bfa768(1198295875584, 3000880, 18732, 6)
ObjectName: Name=select inst_id,hxfil, decode(hxerr, 0,decode(bitand(fhsta, 1), 0,’NOT ACTIVE’,’ACTIVE’), 1,’FILE MISSING’, 2,’OFFLINE NORMAL’, 3,’NOT VERIFIED’, 4,’FILE NOT FOUND’, 5,’CANNOT OPEN FILE’, 6,’CANNOT READ HEADER’, 7,’CORRUPT HEADER’, 8,’WRONG FILE TYPE’, 9,’WRONG DATABASE’, 10,’WRONG FILE NUMBER’, 11,’WRONG FILE CREATE’, 12,’WRONG FILE CREATE’, 16,’DELAYED OPEN’, ‘UNKNOWN ERROR’), to_number(fhbsc), to_date(fhbti,’MM/DD/RR HH24:MI:SS’,’NLS_CALENDAR=Gregorian’), con_id from x$kcvfhonl
ObjectName: Name=select /*+ FIRST_ROWS(1) PARALLEL(“WRH$_SYSTEM_EVENT”, 1) */ 1 from NO_CROSS_CONTAINER(“SYS”.”WRH$_SYSTEM_EVENT”) PARTITION (“WRH$_SYSTEM_EVENT_1073777561_MXSN”) where ( ( ( ( “DBID” < 1073777561 ) ) OR ( “DBID” = 1073777561 AND ( “SNAP_ID” < 4633 ) ) ) ) and rownum < 2
Bucket: #=98 Mutex=0x75bfabe8(1198295875584, 2000584, 2588, 6)
ObjectName: Name=BOOK01P.346f70cda3227d855a05d824c5417817 Child:5
Bucket: #=100 Mutex=0x75bfac48(1198295875584, 2000564, 3009, 6)
ObjectName: Name=CDB$ROOT.SYS.WRI$_EMX_FILE_ID_SEQ
Bucket: #=113 Mutex=0x75bfaeb8(1198295875584, 2000601, 4520, 6)
ObjectName: Name=BOOK01P.SYS.PLITBLM
Bucket: #=116 Mutex=0x75bfaf48(1198295875584, 3000865, 8147, 6)
ObjectName: Name=SELECT XMLELEMENT( “meta”, XMLATTRIBUTES( :B8 AS “dbid”, :B7 AS “dbver”, :B6 AS “snap1”, :B5 AS “snap2”, TO_CHAR(:B4 , :B2 ) AS “time1”, TO_CHAR(:B3 , :B2 ) AS “time2”, :B1 AS “duration”, ‘INSTANCE’ AS “req” )) FROM DUAL
ObjectName: Name=CDB$ROOT.SYS.WRH$_TABLESPACE_STAT
Bucket: #=123 Mutex=0x75bfb098(1198295875584, 2, 0, 6)
ObjectName: Name=BOOK01P.95877b00d73cc02fa74b03d823535a0f Child:1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Bucket: #=126 Mutex=0x75bfb128(1198295875584, 2000622, 3340, 6)
ObjectName: Name=BOOK01P.SYS.SCHEDULER$_WINDOW_DETAILS
Bucket: #=141 Mutex=0x75bfb3f8(1198295875584, 3000935, 11689, 6)
ObjectName: Name=select /*+ FIRST_ROWS(1) PARALLEL(“WRH$_INSTANCE_RECOVERY”, 1) */ 1 from NO_CROSS_CONTAINER(“SYS”.”WRH$_INSTANCE_RECOVERY”) PARTITION (“WRH$_INSTANCE_RECOVERY_1073777561_MXSN”) where ( ( ( ( “DBID” > 1073777561 ) ) OR ( “DBID” = 1073777561 AND ( “SNAP_ID” >= 4633 OR “SNAP_ID” IS NULL ) ) ) ) and rownum < 2
ObjectName: Name=CDB$ROOT.93e93a96b998f32c3c7f46d309c66e2c Child:0
Bucket: #=154 Mutex=0x75bfb668(1198295875584, 2000579, 2747, 6)
ObjectName: Name=BOOK01P.SYS.WRH$_SEG_STAT 1
Bucket: #=162 Mutex=0x75bfb7e8(1198295875584, 2000598, 2975, 6)
ObjectName: Name=BOOK01P.15884431093d45436056120745deca1e Child:0
Bucket: #=180 Mutex=0x75bfbb48(1198295875584, 2000570, 3487, 6)
ObjectName: Name=BOOK01P.74883158531f5451563af5c5330937e3 Child:0
Bucket: #=192 Mutex=0x75bfbd88(1198295875584, 2000715, 53345, 6)
ObjectName: Name=SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s CONNECT BY s.grantee# = PRIOR s.privilege# AND (s.privilege# > 0 OR s.privilege# = -352) START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN (SELECT c1.privilege# FROM sys.codeauth$ c1 WHERE c1.obj# = :1) UNION SELECT c2.privilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2
Bucket: #=195 Mutex=0x75bfbe18(1198295875584, 31, 0, 6)
ObjectName: Name=BOOK01P.SYS.AWR_CDB_RSRC_PLAN
Bucket: #=212 Mutex=0x75bfc148(1198295875584, 30, 0, 6)
–//也就是除了sql语句，还有其他对象比如表，包等待。占用了前面Bucket: #=192 。
–//这样基本验证我的推断从library cache bucket=0开始扫描。如果存在对象给对应的bucket加上mutex X。这样扫描到bucket=192时
–//找到符合条件的记录。
5.使用gdb跟踪看看：
$ cat bucket_mutex.gdb
set pagination off
set logging file bucket_mutex.log
set logging overwrite on
set logging on
set $kmutexget = 1
set $kbucketget = 1
break kglGetBucketMutex
command
silent
printf “kglGetBucketMutex count %02d — Bucket : %d, Location : %d (0x%x)\n”, $kbucketget++ ,$rsi ,$r8d,$r8d
continue
end
break kglGetMutex
command
silent
printf “kglGetMutex count %02d — Mutex address : 0x%x, Location : %d (0x%x)\n”, $kmutexget++ ,$rsi ,$r8d,$r8d
continue
end
define PrintkglMutexLocations
set pagination off
set $i = 0
while $i < $arg0
x /s *((uint64_t *)&kglMutexLocations + $i)
set $i = $i + 1
end
end
define pkglloc
set pagination off
x /s *((uint64_t *)&kglMutexLocations + $arg0 – 1)
end
–//session 1:
SCOTT@book01p> @ spid
==============================
SID : 145
SERIAL# : 64193
PROCESS : 4284
SERVER : DEDICATED
SPID : 4286
PID : 49
P_SERIAL# : 8
KILL_COMMAND : alter system kill session ‘145,64193’ immediate;
PL/SQL procedure successfully completed.
–//windows 1:
$ gdb -f -p 4286 -x bucket_mutex.gdb
(gdb) del break 2
–//session 1:
SCOTT@book01p> @ loop.txt 1
PL/SQL procedure successfully completed.
–//windows 1:
Breakpoint 1 at 0x15370830
(gdb) c
Continuing.
kglGetBucketMutex count 01 — Bucket : 5, Location : 49 (0x31)
kglGetBucketMutex count 02 — Bucket : 5, Location : 145 (0x91)
kglGetBucketMutex count 03 — Bucket : 17, Location : 49 (0x31)
kglGetBucketMutex count 04 — Bucket : 17, Location : 145 (0x91)
kglGetBucketMutex count 05 — Bucket : 39, Location : 49 (0x31)
kglGetBucketMutex count 06 — Bucket : 39, Location : 145 (0x91)
kglGetBucketMutex count 07 — Bucket : 47, Location : 49 (0x31)
kglGetBucketMutex count 08 — Bucket : 47, Location : 145 (0x91)
kglGetBucketMutex count 09 — Bucket : 55, Location : 49 (0x31)
kglGetBucketMutex count 10 — Bucket : 55, Location : 145 (0x91)
kglGetBucketMutex count 11 — Bucket : 67, Location : 49 (0x31)
kglGetBucketMutex count 12 — Bucket : 67, Location : 145 (0x91)
kglGetBucketMutex count 13 — Bucket : 74, Location : 49 (0x31)
kglGetBucketMutex count 14 — Bucket : 74, Location : 145 (0x91)
kglGetBucketMutex count 15 — Bucket : 78, Location : 49 (0x31)
kglGetBucketMutex count 16 — Bucket : 78, Location : 145 (0x91)
kglGetBucketMutex count 17 — Bucket : 98, Location : 49 (0x31)
kglGetBucketMutex count 18 — Bucket : 98, Location : 145 (0x91)
kglGetBucketMutex count 19 — Bucket : 100, Location : 49 (0x31)
kglGetBucketMutex count 20 — Bucket : 100, Location : 145 (0x91)
kglGetBucketMutex count 21 — Bucket : 102, Location : 49 (0x31)
kglGetBucketMutex count 22 — Bucket : 102, Location : 145 (0x91)
kglGetBucketMutex count 23 — Bucket : 113, Location : 49 (0x31)
kglGetBucketMutex count 24 — Bucket : 113, Location : 145 (0x91)
kglGetBucketMutex count 25 — Bucket : 116, Location : 49 (0x31)
kglGetBucketMutex count 26 — Bucket : 116, Location : 145 (0x91)
kglGetBucketMutex count 27 — Bucket : 116, Location : 145 (0x91)
kglGetBucketMutex count 28 — Bucket : 120, Location : 49 (0x31)
kglGetBucketMutex count 29 — Bucket : 120, Location : 145 (0x91)
kglGetBucketMutex count 30 — Bucket : 123, Location : 49 (0x31)
kglGetBucketMutex count 31 — Bucket : 123, Location : 145 (0x91)
kglGetBucketMutex count 32 — Bucket : 126, Location : 49 (0x31)
kglGetBucketMutex count 33 — Bucket : 126, Location : 145 (0x91)
kglGetBucketMutex count 34 — Bucket : 141, Location : 49 (0x31)
kglGetBucketMutex count 35 — Bucket : 141, Location : 145 (0x91)
kglGetBucketMutex count 36 — Bucket : 143, Location : 49 (0x31)
kglGetBucketMutex count 37 — Bucket : 143, Location : 145 (0x91)
kglGetBucketMutex count 38 — Bucket : 159, Location : 49 (0x31)
kglGetBucketMutex count 39 — Bucket : 159, Location : 145 (0x91)
kglGetBucketMutex count 40 — Bucket : 159, Location : 145 (0x91)
kglGetBucketMutex count 41 — Bucket : 192, Location : 49 (0x31)
kglGetBucketMutex count 42 — Bucket : 192, Location : 145 (0x91)
–//基本验证我前面的判断。
(gdb) pkglloc 0x31
0x17adb5b8: “kglic1 49”
(gdb) pkglloc 0x91
0x17adbc10: “kglic4 145”
–//而普通sql语句的情况：
select * from dept where deptno=20;
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
———- ————- ———— ———- ———- ——————- ———–
95129850 80baj2c2ur47u 0 102650 5ab90fa 2025-07-27 12:08:18 16777221
–//KGL_BUCKET=102650
–//执行4次，gdb检测情况如下：
Breakpoint 1 at 0x15370830
–//第1次执行：
(gdb) c
Continuing.
kglGetBucketMutex count 01 — Bucket : 102650, Location : 62 (0x3e)
kglGetBucketMutex count 02 — Bucket : 102650, Location : 62 (0x3e)
kglGetBucketMutex count 03 — Bucket : 72611, Location : 64 (0x40)
kglGetBucketMutex count 04 — Bucket : 72135, Location : 64 (0x40)
kglGetBucketMutex count 05 — Bucket : 31441, Location : 62 (0x3e)
kglGetBucketMutex count 06 — Bucket : 102650, Location : 177 (0xb1)
kglGetBucketMutex count 07 — Bucket : 102650, Location : 179 (0xb3)
kglGetBucketMutex count 08 — Bucket : 72611, Location : 64 (0x40)
kglGetBucketMutex count 09 — Bucket : 72135, Location : 64 (0x40)
kglGetBucketMutex count 10 — Bucket : 23529, Location : 62 (0x3e)
kglGetBucketMutex count 11 — Bucket : 72611, Location : 64 (0x40)
kglGetBucketMutex count 12 — Bucket : 72135, Location : 64 (0x40)
kglGetBucketMutex count 13 — Bucket : 98828, Location : 62 (0x3e)
kglGetBucketMutex count 14 — Bucket : 55783, Location : 62 (0x3e)
kglGetBucketMutex count 15 — Bucket : 69906, Location : 62 (0x3e)
kglGetBucketMutex count 16 — Bucket : 89962, Location : 62 (0x3e)
kglGetBucketMutex count 17 — Bucket : 60655, Location : 62 (0x3e)
kglGetBucketMutex count 18 — Bucket : 96279, Location : 62 (0x3e)
kglGetBucketMutex count 19 — Bucket : 97795, Location : 62 (0x3e)
kglGetBucketMutex count 20 — Bucket : 127944, Location : 62 (0x3e)
kglGetBucketMutex count 21 — Bucket : 10224, Location : 62 (0x3e)
kglGetBucketMutex count 22 — Bucket : 24312, Location : 62 (0x3e)
kglGetBucketMutex count 23 — Bucket : 36799, Location : 62 (0x3e)
kglGetBucketMutex count 24 — Bucket : 94595, Location : 62 (0x3e)
kglGetBucketMutex count 25 — Bucket : 124988, Location : 62 (0x3e)
kglGetBucketMutex count 26 — Bucket : 20307, Location : 62 (0x3e)
kglGetBucketMutex count 27 — Bucket : 106042, Location : 62 (0x3e)
kglGetBucketMutex count 28 — Bucket : 95710, Location : 62 (0x3e)
kglGetBucketMutex count 29 — Bucket : 59063, Location : 62 (0x3e)
kglGetBucketMutex count 30 — Bucket : 95710, Location : 179 (0xb3)
kglGetBucketMutex count 31 — Bucket : 110580, Location : 62 (0x3e)
kglGetBucketMutex count 32 — Bucket : 113172, Location : 62 (0x3e)
kglGetBucketMutex count 33 — Bucket : 113172, Location : 62 (0x3e)
kglGetBucketMutex count 34 — Bucket : 105175, Location : 62 (0x3e)
kglGetBucketMutex count 35 — Bucket : 21761, Location : 62 (0x3e)
kglGetBucketMutex count 36 — Bucket : 59274, Location : 62 (0x3e)
kglGetBucketMutex count 37 — Bucket : 106042, Location : 62 (0x3e)
–//第2次执行：
kglGetBucketMutex count 38 — Bucket : 102650, Location : 62 (0x3e)
–//第3次执行：
kglGetBucketMutex count 39 — Bucket : 102650, Location : 62 (0x3e)
–//第4次执行，没有输出，sql语句已经缓存，绕过了kglGetBucketMutex。
–//顺便提一下如果在cdb上执行@loop.txt 1,输出如下，前面多访问Bucket=72611。
(gdb) c
Continuing.
kglGetBucketMutex count 18 — Bucket : 72611, Location : 64 (0x40)
kglGetBucketMutex count 19 — Bucket : 72611, Location : 64 (0x40)
kglGetBucketMutex count 20 — Bucket : 5, Location : 49 (0x31)
kglGetBucketMutex count 21 — Bucket : 5, Location : 145 (0x91)
kglGetBucketMutex count 22 — Bucket : 14, Location : 49 (0x31)
kglGetBucketMutex count 23 — Bucket : 14, Location : 145 (0x91)
kglGetBucketMutex count 24 — Bucket : 17, Location : 49 (0x31)
kglGetBucketMutex count 25 — Bucket : 17, Location : 145 (0x91)
kglGetBucketMutex count 26 — Bucket : 39, Location : 49 (0x31)
kglGetBucketMutex count 27 — Bucket : 39, Location : 145 (0x91)
kglGetBucketMutex count 28 — Bucket : 45, Location : 49 (0x31)
kglGetBucketMutex count 29 — Bucket : 45, Location : 145 (0x91)
kglGetBucketMutex count 30 — Bucket : 47, Location : 49 (0x31)
kglGetBucketMutex count 31 — Bucket : 47, Location : 145 (0x91)
–//前面多了一次访问Bucket : 72611,每次执行都是如此。
–//查看转储，访问对象是CDB$ROOT.SYS.book。
Bucket: #=72611 Mutex=0x75f4d9c8(1198295875584, 25094, 3, 6)
LibraryHandle: Address=0x72e5c260 Hash=a6411ba3 LockMode=0 PinMode=0 LoadLockMode=0 Status=0 Subpool=1
ObjectName: Name=CDB$ROOT.SYS.book
FullHashValue=b885bb910059a4d2fdbfc245a6411ba3 Namespace=DBINSTANCE(74) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=1 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=0 TotalLockCount=25071 TotalPinCount=0
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
Concurrency: DependencyMutex=0x72e5c310(0, 0, 0, 0) Mutex=0x72e5c3b8(279, 50165, 0, 6)
Flags=RON/PIN/KEP/BSO/[00810003] Flags2=[0000]
WaitersLists:
Lock=0x72e5c2f0[0x72e5c2f0,0x72e5c2f0]
Pin=0x72e5c2d0[0x72e5c2d0,0x72e5c2d0]
LoadLock=0x72e5c348[0x72e5c348,0x72e5c348]
Timestamp:
HandleReference: Address=0x72e5c438 Handle=(nil) Flags=[00]
6.特殊测试：
–//我以前为了测试需要收集了一些特殊sql语句，其KGL_BUCKET=0。
select /*+ 9 */ count(*) from dept where deptno = 93834;
select /*+ 9 */ count(*) from dept where deptno = 93091;
–//先执行以上语句多次，重复测试。这样P1应该仅仅看到0的情况。
$ zzdate ; seq 50 | xargs -P 50 -IQ sqlplus scott/book@book01p @record.txt 2e4 aaaa loop.txt > /dev/null; zzdate
trunc(sysdate)+12/24+23/1440+49/86400 1753590229.274249982
trunc(sysdate)+12/24+24/1440+01/86400 1753590241.099016484
SYS@book> @ ashtop event,sql_id 1=1 trunc(sysdate)+12/24+23/1440+49/86400 trunc(sysdate)+12/24+24/1440+01/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- —————————————— ————- ——————- ——————- ———- ——– ———–
344 28.7 84% | library cache: bucket mutex X 6s0qhxyxjpurx 2025-07-27 12:23:50 2025-07-27 12:24:00 344 11 344
42 3.5 10% | 6s0qhxyxjpurx 2025-07-27 12:23:50 2025-07-27 12:23:58 41 9 42
9 .7 2% | fm1tb5mss6fsj 2025-07-27 12:23:51 2025-07-27 12:23:56 9 5 9
6 .5 1% | library cache: mutex X 6s0qhxyxjpurx 2025-07-27 12:23:51 2025-07-27 12:24:00 6 5 6
3 .2 1% | 2025-07-27 12:23:49 2025-07-27 12:23:56 1 3 3
2 .2 0% | cursor: pin S 2025-07-27 12:23:52 2025-07-27 12:23:55 1 2 2
1 .1 0% | cursor: pin S 6s0qhxyxjpurx 2025-07-27 12:23:55 2025-07-27 12:23:55 1 1 1
1 .1 0% | log file sync 2025-07-27 12:23:51 2025-07-27 12:23:51 1 1 1
8 rows selected.
SYS@book> @ ashtop event,p1raw,p1,p3raw “event=’library cache: bucket mutex X'” trunc(sysdate)+12/24+23/1440+49/86400 trunc(sysdate)+12/24+24/1440+01/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT P1RAW P1 P3RAW FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- —————————————— —————– ———- —————– ——————- ——————- ———- ——– ———–
190 15.8 55% | library cache: bucket mutex X 0000000000000000 0 0000000000000031 2025-07-27 12:23:50 2025-07-27 12:24:00 190 11 190
154 12.8 45% | library cache: bucket mutex X 0000000000000000 0 0000000000000091 2025-07-27 12:23:50 2025-07-27 12:24:00 154 11 154
7.继续测试：
–//修改loop.txt 脚本，仅仅访问v$session视图看看：
declare
v_count number;
begin
for i in 1 .. &&1 loop
select count(*) into v_count from v$session ;
–select count(*) into v_count from v$sql ;
–//select count(*) into v_count from v$sql where rownum<=1;
end loop;
end ;
/
$ zzdate ; seq 50 | xargs -P 50 -IQ sqlplus scott/book@book01p @record.txt 1e4 aaaa loop.txt > /dev/null; zzdate
trunc(sysdate)+12/24+26/1440+54/86400 1753590414.086612651
trunc(sysdate)+12/24+27/1440+24/86400 1753590444.014456173
SYS@book> @ ashtop event,sql_id 1=1 trunc(sysdate)+12/24+26/1440+54/86400 trunc(sysdate)+12/24+27/1440+24/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
——— ——- ——- —————————————— ————- ——————- ——————- ———- ——– ———–
1173 39.1 95% | 6yrc6byy4fkdh 2025-07-27 12:26:54 2025-07-27 12:27:23 1163 30 1169
26 .9 2% | cursor: pin S 2025-07-27 12:27:02 2025-07-27 12:27:14 1 3 3
21 .7 2% | 6p2rfss035gya 2025-07-27 12:26:55 2025-07-27 12:27:22 20 13 20
19 .6 2% | 2025-07-27 12:26:55 2025-07-27 12:27:18 1 13 13
1 .0 0% | cursor: pin S 6yrc6byy4fkdh 2025-07-27 12:27:19 2025-07-27 12:27:19 1 1 1
–//这次就看不到library cache: bucket mutex X 等待事件。主要消耗CPU外集中在cursor: pin S。
–//估计如果底层视图涉及x$kgl开头的表都存在类似情况。
SYS@book> @ xt x$kgl
INST_ID NAME OBJECT_ID TYPE TABLE_NUM CON_ID
———- —————————— ———- ——- ———- ———-
1 X$KGLSIM 4294951882 TABLE 98 0
1 X$KGLJSIM 4294951994 TABLE 99 0
1 X$KGLMEM 4294951885 TABLE 100 0
1 X$KGLJMEM 4294951997 TABLE 101 0
1 X$KGLOB 4294950985 TABLE 665 0
1 X$KGLOBXML 4294953144 TABLE 666 0
1 X$KGLLK 4294950986 TABLE 667 0
1 X$KGLPN 4294950987 TABLE 668 0
1 X$KGLST 4294950988 TABLE 669 0
1 X$KGLAU 4294951112 TABLE 670 0
1 X$KGLSN 4294951379 TABLE 671 0
1 X$KGLTR 4294951033 TABLE 672 0
1 X$KGLXS 4294951034 TABLE 673 0
1 X$KGLRD 4294951187 TABLE 674 0
1 X$KGLDP 4294951035 TABLE 675 0
1 X$KGLNA 4294951064 TABLE 676 0
1 X$KGLNA1 4294951150 TABLE 677 0
1 X$KGLTABLE 4294951056 TABLE 65537 0
1 X$KGLBODY 4294951057 TABLE 65537 0
1 X$KGLTRIGGER 4294951058 TABLE 65537 0
1 X$KGLINDEX 4294951059 TABLE 65537 0
1 X$KGLCLUSTER 4294951060 TABLE 65537 0
1 X$KGLCURSOR 4294951061 TABLE 65537 0
1 X$KGLCURSOR_CHILD_SQLID 4294952684 TABLE 65537 0
1 X$KGLCURSOR_CHILD_SQLIDPH 4294952680 TABLE 65537 0
1 X$KGLCURSOR_CHILD 4294952683 TABLE 65537 0
1 X$KGLCURSOR_PARENT 4294953372 TABLE 65537 0
1 X$KGLSQLTXL 4294953759 TABLE 65537 0
28 rows selected.
8.总结：
–//1.注意library cache: bucket mutex X与library cache: mutex X的区别，以前11g版本(包含11g)都是library cache: mutex X，
–//可以结合p1raw，p3raw判断。
–//2.一些访问以底层视图x$kgl开头的视图，会出现library cache: bucket mutex X，扫描方法与我前面介绍类似。在生产系统应该尽
–//量规避这类扫描操作。像一些垃圾监控访问v$sqlarea 排序取物理读以及逻辑读和执行时间最多的语句根本没必要，主要生产系统共
–//享池一般都非常大,扫描1次需要花费大量时间。
–//3.如果需要访问尽可能通过x$索引.
SYS@book> @ xind x$kglcursor_child,x$kglob
DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
—————————— —————————— ———— —————————— ————— ———-
X$KGLOB 1 KGLNAHSH 0 0
X$KGLOB 2 KGLOBT03 0 0
DERIVED_TABLES TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
—————————— —————————— ———— —————————— ————— ———-
X$KGLCURSOR_CHILD X$KGLOB 1 KGLNAHSH 0 0
X$KGLCURSOR_CHILD X$KGLOB 2 KGLOBT03 0 0
–//比如访问v$sql通过sql_id或者hash_value都很快，大家可以前面gdb脚本测试：
SCOTT@book01p> select * from dept where deptno=20;
DEPTNO DNAME LOC
———- —————————— ————-
20 RESEARCH DALLAS
SCOTT@book01p> @ hashz
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET HASH_HEX SQL_EXEC_START SQL_EXEC_ID
———- ————- ———— ———- ———- ——————- ———–
95129850 80baj2c2ur47u 0 102650 5ab90fa 2025-07-27 16:39:50 16777221
SCOTT@book01p> select sql_id,sql_text from v$sql where sql_id=’80baj2c2ur47u’;
SQL_ID SQL_TEXT
————- ————————————————————
80baj2c2ur47u select * from dept where deptno=20
(gdb) del break 2
(gdb) c
Continuing.
kglGetBucketMutex count 03 — Bucket : 102650, Location : 49 (0x31)
kglGetBucketMutex count 04 — Bucket : 102650, Location : 145 (0x91)
SCOTT@book01p> select sql_id,sql_text from v$sql where hash_value=95129850;
SQL_ID SQL_TEXT
————- ————————————————————
80baj2c2ur47u select * from dept where deptno=20
–//gdb输出。
kglGetBucketMutex count 32 — Bucket : 90919, Location : 62 (0x3e)
kglGetBucketMutex count 33 — Bucket : 102650, Location : 49 (0x31)
kglGetBucketMutex count 34 — Bucket : 102650, Location : 145 (0x91)
kglGetBucketMutex count 35 — Bucket : 102650, Location : 49 (0x31)
kglGetBucketMutex count 36 — Bucket : 102650, Location : 145 (0x91)
–//有点怀疑其实根本没有怎么索引，仅仅通过sql_id,hash_value 推导出bucket的值。
–//95129850%2^17 = 102650
SCOTT@book01p> @ xtoy 80baj2c2ur47u 32 16
BASE32 BASE16 BASE10
—————————— —————————— ——————————
80baj2c2ur47u 802d511305ab90fa 9236127552948113658
–//base16=802d511305ab90fa.
(gdb) p/d 0x802d511305ab90fa & 0xffffffff
$2 = 95129850
(gdb) p/d 0x802d511305ab90fa & 0x1ffff
$1 = 102650
–//前面出现Bucket : 90919对应sql语句，再次执行就不出现了。
SYS@book> select sql_id ,hash_value,mod(hash_value,power(2,17)) from v$sql where mod(hash_value,power(2,17))= 90919;
SQL_ID HASH_VALUE MOD(HASH_VALUE,POWER(2,17))
————- ———- —————————
ddayh21y4kst7 2085184295 90919
SYS@book> @ sql_id ddayh21y4kst7
— SQL_ID = ddayh21y4kst7 come from shared pool
select sql_id,sql_text from v$sql where hash_value=95129850;
9.附上测试使用脚本：
$ cat xt.sql
— Copyright 2023 lfree. All rights reserved.
— Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
——————————————————————————–
—
— File name: xt.sql
— Purpose: query x$ table
— Author: lfree
—
— Usage:
— @ xt <regexp_like_string>
— replace middle $ using \$ , last $ do not replace.
——————————————————————————–
set termout off
column column_string new_value column_string format a200
–select replace(‘&1′,’$’,’\$’) column_string from dual;
select decode(instr(reverse(‘&1′),’$’),1, replace(substr(‘&&1’,1,length(‘&&1′)-1),’$’,’\$’)||’$’ , replace(‘&1′,’$’,’\$’)) column_string from dual;
set termout on
select * from gv$fixed_table where regexp_like (name,’&column_string’,’i’);
$ cat xind.sql
— Copyright 2023 lfree. All rights reserved.
— Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
——————————————————————————–
—
— File name: xind.sql
— Purpose: query X$ index
— Author: lfree
—
— Usage:
— @ xind <x$table_name,…>
— @ xind <x$kglob,x$kgldp>
——————————————————————————–
set termout off
column column_string new_value column_string format a200
–select decode(‘&1′,”,’1=1′,’1′,’1=1′,’1=1′,’1=1’,’table_name in (‘||””||replace(upper(‘&1′),’,’,”’,”’)||”’)’ ) column_string1 from dual ;
–select decode(‘&1′,”,’1=1′,’1′,’1=1′,’1=1′,’1=1’,””||replace(upper(‘&1′),’,’,”’,”’)||””) column_string from dual ;
select ””||replace(upper(‘&1′),’,’,”’,”’)||”” column_string from dual ;
set termout on
–//select * from V$INDEXED_FIXED_COLUMN where 1=1 and table_name in (&column_string);
set feedback off
column derived_tables format a30
SELECT NULL derived_tables, V$INDEXED_FIXED_COLUMN.*
FROM V$INDEXED_FIXED_COLUMN
WHERE 1 = 1 AND table_name in (&column_string)
— WHERE 1 = 1 AND regexp_like (table_name ,upper(‘&1’))
;
SELECT x$kqfdt.KQFDTNAM derived_tables, V$INDEXED_FIXED_COLUMN.*
FROM V$INDEXED_FIXED_COLUMN, x$kqfdt
WHERE 1 = 1
AND V$INDEXED_FIXED_COLUMN.table_name = x$kqfdt.KQFDTEQU
AND x$kqfdt.kqfdtnam in (&column_string)
— AND regexp_like ( x$kqfdt.kqfdtnam ,upper(‘&1’))
order by x$kqfdt.KQFDTNAM ,INDEX_NUMBER
;
prompt
set feedback 6
来源链接：https://www.cnblogs.com/lfree/p/19015913
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容