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/,如需转载,请注明出处,否则将追究法律责任。