[20170727]library cache: mutex X.txt
--//如果多个会话访问v$sql视图,其底层视图是x$kglcursor_child,如果几个会话同时访问,会出现library cache: mutex X等待事件,通
--//过例子说明:
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ cat b3.sql
declare
c integer;
begin
for i in 1..&1
loop
execute immediate 'select count(*) from v$sql' into c;
end loop;
end;
/
quit
$ cat b1.sh
#! /bin/bash
for i in $(seq 10); do
sqlplus -s -l / as sysdba @b3.sql 1e5 &
done
$ cat wait.sql
SELECT p1raw
,p2raw
,p3raw
,p1
,p2
,p3
,sid
,serial#
,seq#
,event
,state
,wait_time_micro
,seconds_in_wait
FROM v$session
WHERE wait_class <> 'Idle'
ORDER BY event;
2.测试:
$ . b1.sh
-/打开另外的会话测试:
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ------------ ------------ --- ------------ ------------ ------------ ------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 54 39 38 SQL*Net message to client WAITED SHORT TIME 1 0
00000000000047C7 000000B800000000 0000000000000031 18375 790273982464 49 80 27 1283 library cache: mutex X WAITING 841 0
000000000000503B 000000AB00000000 0000000000000031 20539 734439407616 49 94 7 1552 library cache: mutex X WAITED SHORT TIME 4 0
0000000000000BF0 000000B800000000 0000000000000031 3056 790273982464 49 106 5 1285 library cache: mutex X WAITING 1435 0
000000000001EACE 00 0000000000000031 125646 0 49 184 3 1136 library cache: mutex X WAITED SHORT TIME 2 0
00000000000001C4 0000005E00000000 0000000000000031 452 403726925824 49 132 3 1466 library cache: mutex X WAITED SHORT TIME 2 0
000000000000ECBA 00 0000000000000031 60602 0 49 144 5 1607 library cache: mutex X WAITED SHORT TIME 1065 0
000000000001FC45 00 0000000000000031 130117 0 49 158 3 1467 library cache: mutex X WAITED SHORT TIME 2 0
0000000000018D9D 00 0000000000000031 101789 0 49 171 3 1559 library cache: mutex X WAITED SHORT TIME 3 0
0000000000003DE1 000000B800000000 0000000000000031 15841 790273982464 49 67 69 1318 library cache: mutex X WAITED SHORT TIME 2 0
000000000001EACE 00 0000000000000031 125646 0 49 119 3 1535 library cache: mutex X WAITED SHORT TIME 3 0
11 rows selected.
SYS@book> select * from V$EVENT_NAME where name='library cache: mutex X' ;
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------- ------------ ---------------------- ---------- ---------- ---------- ------------- ------------ --------------------
289 1646780882 library cache: mutex X idn value where 3875070507 4 Concurrency
SYS@book> column location format a40
SYS@book> select * from (select * from V$MUTEX_SLEEP where mutex_type='Library Cache' order by 3 desc) where rownum<=5;
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
-------------------- ---------------------------------------- ------------ ------------
Library Cache kglic1 49 1540346 0
Library Cache kglReleaseHandleReference 125 19223 0
Library Cache kglGetHandleReference 124 10570 0
Library Cache kgllkdl1 85 16 0
Library Cache kglllal3 111 13 0
--//这里的49应该对应前面的P3.
--//如果应用老是执行相同的sql语句,出现最多的等待事件是cursor: pin S .大家可以自行测试.
