Mutexes機制及其等待事件
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Shared Pool機制之——Latches, Locks, Pins and MutexesOracleMutex
- 淺談JS事件機制與React事件機制JS事件React
- 【等待事件】ORACLE常見等待事件事件Oracle
- 【等待事件】等待事件系列（5.1）--Enqueue(佇列等待)事件ENQ佇列
- DOM事件機制事件
- react事件機制React事件
- redis事件機制Redis事件
- qt事件機制QT事件
- 等待事件事件
- http快取機制及其原理HTTP快取
- wait等待事件及其處理方法 awr top5 報告AI事件
- Redis的事件機制Redis事件
- View事件機制分析View事件
- Android 事件機制Android事件
- C# 事件機制C#事件
- Java併發之等待/通知機制Java
- JavaScript執行緒機制與事件機制JavaScript執行緒事件
- Javascript事件模型系列（二）事件的捕獲-冒泡機制及事件委託機制JavaScript事件模型
- JS的事件物件與事件機制JS事件物件
- 【TUNE_ORACLE】等待事件之等待事件類別Oracle事件
- 等待事件分析事件
- oracle等待事件Oracle事件
- Oracle 等待事件Oracle事件
- px等待事件事件
- 等待事件 二事件
- 【等待事件】等待事件系列（1）--User I/O型別事件型別
- 【效能調整】等待事件(三) 常見等待事件（一）事件
- 【效能調整】等待事件(四) 常見等待事件（二）事件
- 多執行緒之等待通知機制執行緒
- Redis 事件機制詳解Redis事件
- JS 事件機制 Event LoopJS事件OOP
- 【React深入】React事件機制React事件
- JavaScript事件迴圈機制JavaScript事件
- Qt 事件機制 學習QT事件
- JavaScript 事件迴圈機制JavaScript事件
- View事件分發機制View事件
- Remoting事件機制續REM事件
- 強制等待