[20170727]library cache: mutex X.txt

[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 .大家可以自行測試.