[20250727]关于访问系统视图v$sqlXXXX的问题.txt
关于访问系统视图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' and '&2' select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum /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 @ 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 select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum select sql_id ,hash_value,mod(hash_value,power(2,17)),FIRST_LOAD_TIME,con_id from v$sql where rownum @ mutexprofz idn,hash,loc,maddr "ts>=trunc(sysdate)+10/24+12/1440+36/86400 and ts
页:
[1]