lfree 動態日志
[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.0x91 = 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
