ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Mutexes机制及其等待事件

Mutexes机制及其等待事件

原创 Linux操作系统 作者:wuhesheng 时间:2009-06-24 15:01:57 0 删除 编辑

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

上一篇: 打开 AUTOTRACE
请登录后发表评论 登录
全部评论

注册时间:2008-08-20

  • 博文量
    34
  • 访问量
    64671