Mutexes機制及其等待事件

wuhesheng發表於2009-06-24
Mutex事件

10G開始，引入mutexes機制用以代替library cache pin
關於mutexes的一些解釋：
轉自http://space.itpub.net/?uid-756652-action-viewspace-itemid-348176

Mutexes are new thing in 10.2 and they enable shared access to objects in somewhat similar manner than shared latches, that every successful get of particular mutex will increment its value and release will decrement. When the count is zero, no-one has the mutex and it is safe to get it in exclusive mode too. However they are more fine grained than kgl latches and provide better waiting mechanism as far as I understand.


關於mutexes機制的一些文章，還可以看玉面飛龍BLOG中的兩篇文章：
http://yumianfeilong.com/2007/05/23/mutexes-in-oracle10g/
http://yumianfeilong.com/2008/11/01/cursor-pin-s/

使用mutexes需要CAS的支援(啥是CAS，還沒搞明白，汗），以下是一個相關的BUG：

Doc ID: 433631.1
Mutex Latch Spin Causes High Cpu on Non-CAS Platforms (HP-UX PA-RISC )

10.2.0.2 defaults the use of mutexes for certain shared cursor operations,
instead of library cache latches and library cache pin latches and library
cache pins. Mutexes use the CAS (compare and swap) operation.

關於CAS，可以看eygle BLOG中的一篇文章：
http://www.eygle.com/digest/2008/08/compare-and-swap.html

由與mutexes機制引入，導致在mutex上競爭而產生的等待事件cursor: pin S wait on X
而與cursor: pin S wait on X等待事件相關的BUG還是比較多的：

Bug 5907779 - “CURSOR: PIN S WAIT ON X” RUNNING DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC

Bug 6084876 - CATUPGRD.SQL HANGS ON CURSOR: PIN S WAIT ON X

以及SGA自動調整太頻繁，也有可能引起cursor: pin S wait on X ：

Doc ID: 742599.1 FREQUENT RESIZE OF SGA
Note 6528336.8 - Bug 6528336 - Automatic SGA may repeatedly shrink / grow the shared pool

通過隱含引數”_kks_use_mutex_pin”可以控制是否使用”mutexes機制”

v$session_wait中cursor: pin S wait on X等待事件各引數的解釋：

Parameter Description
· P1 Hash value of cursor
· P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
· P3 Mutex where (an internal code locator) OR’d with Mutex Sleeps

以下是一個由於使用者非正常退出而導致的cursor: pin S wait on X等待的處理：

檢視等待事件，發現有較多的cursor: pin S wait on X

SYS@DB1> @sw_10
INST_ID EVENT                                   USERNAME         SQL_HASH_VALUE   COUNT(*)
------- --------------------------------------- --------------- --------------- ----------
      2 row cache lock                          FJ                   1020007160          1
      1 db file sequential read                 FJ                   2819896036          1
      1 db file sequential read                 FJ                   3255633308          1
      2 db file sequential read                 FJ                   1191708498          1
      2 row cache lock                          FJ                   1085640872          1
      2 PX Deq: reap credit                     SYS                  2334880858          1
      1 gcs log flush sync                                                    0          1
      2 db file sequential read                 FJ                    865179373          1
      1 latch: cache buffers chains             US                   1864621627          1
      2 SQL*Net more data to client             SQ_SELECT             830074779          1
      1 db file sequential read                 FJ                    649362106          1
      2 db file sequential read                 FJ                   2888637068          1
      2 gc cr request                           US                   1864621627          1
      2 cursor: pin S wait on X                 US                   2674323898          1
      1 gc cr request                           FJ                    943499101          1
      1 log file parallel write                                               0          1
      2 db file sequential read                 FJ                   3270882645          1
      2 db file parallel read                   FJ                     25696630          1
      2 log file sync                           FJ                            0          1
      1 log file sync                           FJ                            0          2
      2 cursor: pin S wait on X                 ALL_SELECT           2674323898         11
      2 cursor: pin S wait on X                 KC                   2674323898         11
      2 cursor: pin S wait on X                 KC_SELECT            2674323898         13
      2 cursor: pin S wait on X                 PC                   2674323898         14
      2 cursor: pin S wait on X                 FJ                   2674323898         15

找出堵住的會話：根據P2RAW這個欄位來：

SYS@DB1> select inst_id,event,p2raw from gv$session_wait where event='cursor: pin S wait on X';
INST_ID EVENT                                   P2RAW
------- --------------------------------------- ----------------
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
      2 cursor: pin S wait on X                 00000FD900000000
已選擇67行。

P2PAW的前兩個欄位，就是以exclusive模式持有mutex的會話，也就是堵住其它會話的會話，可以看出，是同一個會話：

SYS@DB1> select to_number('00000FD9','xxxxxxxxxxxxxx') from dual;
TO_NUMBER('00000FD9','XXXXXXXXXXXXXX')
--------------------------------------
                                  4057

獲取SESSION的相關資訊：

SYS@DB1> @get_session
輸入 input_sid 的值:  4057
 INST_ID     SID USERNAME        TYPE       STATUS   OSUSER          PROCESS      MACHINE              PROGRAM
-------- ------- --------------- ---------- -------- --------------- ------------ -------------------- --------------
       2    4057 FJ              USER       ACTIVE   ganjh           6004:3188    WORKGROUP\ASIAINFO-G plsqldev.exe
 
SPID          INST_ID     SID    SERIAL#  SQL_HASH_VALUE  PREV_HASH_VALUE LOGON_TIME        LAST_CALL_ET
------------ -------- ------- ---------- --------------- ---------------- ---------------- -------------
1114704             2    4057      36794      2674323898       2097682672 2009-01-01 00:29         25910
 
SYS@zjocs2> @sql_hash
輸入 hash_value 的值:  2674323898
SQL_TEXT
----------------------------------------------------------------
begin   sys.dbms_output.get_line(line => :line, status => :statu
s); end;

發現是個人使用者的程式，被堵住的也全是個人會話：

SYS@DB1> select inst_id,username,status,sql_hash_value,osuser,machine,LOGON_TIME from gv$session
  2   where sql_hash_value='2674323898';
 INST_ID USERNAME        STATUS    SQL_HASH_VALUE OSUSER          MACHINE              LOGON_TIME
-------- --------------- -------- --------------- --------------- -------------------- ----------------
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:50
       2 ALL_SELECT      ACTIVE        2674323898 xiaodong.chen   CTZJ\DONG            2009-01-01 00:08
       2 KC              ACTIVE        2674323898 shenyi          WORKGROUP\ASIAINFO-5 2009-01-01 00:36
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 00:36
       2 FJ              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2009-01-01 01:15
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 02:19
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 01:22
       2 ALL_SELECT      ACTIVE        2674323898 Administrator   WORKGROUP\ASIAINFO-A 2008-12-31 13:44
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 00:29
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:05
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 00:53
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:17
       2 KC              ACTIVE        2674323898 fanyl           WORKGROUP\ASIAINFO-3 2008-12-31 22:19
       2 KC              ACTIVE        2674323898 shenyi          WORKGROUP\ASIAINFO-5 2008-12-31 15:25
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 01:50
       2 FJ              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2008-12-31 17:34
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:02
       2 SYS             ACTIVE        2674323898 NiceDream       WORKGROUP\OONICEDREA 2009-01-01 07:39
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:51
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:48
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:05
       2 FJ              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2009-01-01 01:33
       2 KC              ACTIVE        2674323898 Administrator   WORKGROUP\LUNCHEER   2009-01-01 06:06
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 01:05
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:48
       2 SYS             ACTIVE        2674323898 NiceDream       WORKGROUP\OONICEDREA 2009-01-01 07:41
       2 ALL_SELECT      ACTIVE        2674323898 xiaodong.chen   CTZJ\DONG            2009-01-01 02:13
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 01:25
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:10
       2 FJ              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2009-01-01 01:35
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 02:09
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:18
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:47
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 01:29
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:47
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:02
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 02:25
       2 FJ              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2009-01-01 01:03
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 01:00
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:03
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:37
       2 FJ              ACTIVE        2674323898 davy?hu         MSHOME\ASIAINFO-DAVY 2009-01-01 05:34
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 00:55
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 00:55
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 01:02
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:17
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:57
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2008-12-31 23:24
       2 FJ              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:23
       2 FJ              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2009-01-01 04:11
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 00:58
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:51
       2 FJ              ACTIVE        2674323898 davy?hu         MSHOME\ASIAINFO-DAVY 2008-12-31 19:03
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 02:01
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 00:47
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 00:55
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:44
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:45
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:23
       2 FJ              ACTIVE        2674323898 ganjh           WORKGROUP\ASIAINFO-G 2009-01-01 01:24
       2 KC              ACTIVE        2674323898 qinsl           ASIAINFO\QIN-SL      2009-01-01 00:57
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 03:43
       2 KC_SELECT       ACTIVE        2674323898 jenhy           WORKGROUP\CJH        2009-01-01 01:32
       2 US              ACTIVE        2674323898 Administrator   ASIAINFO\XIEQF       2008-12-31 23:38
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 00:57
       2 ALL_SELECT      ACTIVE        2674323898 imlane          WORKGROUP\LENOVO-090 2009-01-01 00:58
       2 PC              ACTIVE        2674323898 HUMIN           WORKGROUP\PHOENIX    2009-01-01 04:05
       2 KC              ACTIVE        2674323898 shenyi          WORKGROUP\ASIAINFO-5 2009-01-01 00:47

登陸到2機上，殺掉該會話，等待馬上消失：

SYS@zjocs2>  alter system kill session '4057,36794';
 alter system kill session '4057,36794'
*
ERROR 位於第 1 行:
ORA-00031: session marked for kill
 
SYS@zjocs2> /
INST_ID EVENT                                   USERNAME         SQL_HASH_VALUE   COUNT(*)
------- --------------------------------------- --------------- --------------- ----------
      2 latch: cache buffers chains             ZG                    573107790          1
      1 gc current request                      FJ                   3255633308          1
      2 gc cr request                           FJ                   2508982106          1
      2 db file sequential read                 FJ                   1524627125          1
      1 db file sequential read                 KC                   3289201399          1
      1 PX Deq Credit: send blkd                SYS                   208139285          1
      1 gcs log flush sync                                                    0          1
      1 log file sync                           FJ                            0          1
      1 latch: cache buffers chains             US                   1864621627          1
      2 latch: cache buffers chains             ZG                   2425578323          1
      2 SQL*Net more data to client             SQ_SELECT             830074779          1
      2 db file sequential read                 FJ                    413943445          1
      2 db file sequential read                 FJ                    405457470          1
      1 PX Deq: reap credit                     SYS                  2334880858          1
      2 library cache lock                      FJ                   3081328415          1
      2 db file sequential read                 ZG                    598173855          1
      2 gc cr request                           FJ                    255537769          1
      1 log file parallel write                                               0          1
      2 gc cr request                           US                   1864621627          1
      2 cursor: pin S wait on X                 US                   2674323898          1
      2 cursor: pin S wait on X                 SYS                  2674323898          2
      2 cursor: pin S wait on X                 ALL_SELECT           2674323898         11
      2 cursor: pin S wait on X                 KC                   2674323898         11
      2 cursor: pin S wait on X                 KC_SELECT            2674323898         13
      2 cursor: pin S wait on X                 PC                   2674323898         14
      2 cursor: pin S wait on X                 FJ                   2674323898         15
SYS@zjocs2> /
INST_ID EVENT                                   USERNAME         SQL_HASH_VALUE   COUNT(*)
------- --------------------------------------- --------------- --------------- ----------
      1 log file sync                           FJ                            0          1
      2 db file sequential read                 FJ                   1090936777          1
      2 wait for scn ack                                                      0          1
      2 db file sequential read                 FJ                    487923415          1
      2 gc cr request                           FJ                   1015004348          1
      2 SQL*Net more data to client             SQ_SELECT             830074779          1
      1 PX Deq: reap credit                     SYS                  2334880858          1
      1 log file parallel write                                               0          1
      2 gc cr request                           US                   1864621627          1
      2 gc cr request                           FJ                   3442803315          1
      2 log file sync                           FJ                            0          1
      1 db file sequential read                 FJ                   3255633308          1
      1 latch: cache buffers chains             US                   1864621627          1
      2 db file sequential read                 FJ                   3251065584          1
      1 gcs log flush sync                                                    0          2

— The End —

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/119501/viewspace-607415/，如需轉載，請註明出處，否則將追究法律責任。

相關文章