本文共 3370 字，大约阅读时间需要 11 分钟。
[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 ConcurrencySYS@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 .大家可以自行测试.转载地址：http://keeql.baihongyu.com/