深入理解Oracle中的Mutex(5)

　　接着我们会在环境中模拟cursor pin S wait on X的场景，并通过systemstate dump和v$mutex_sleep , v$mutex_sleep_history等视图观察这一现象

　　session A:

　　SQL> select * from v$version;

　　BANNER

　　—————————————————————-

　　Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

　　PL/SQL Release 10.2.0.5.0 - Production

　　CORE 10.2.0.5.0 Production

　　TNS for Linux: Version 10.2.0.5.0 - Production

　　NLSRTL Version 10.2.0.5.0 - Production

　　www.askmaclean.com

　　SQL> show parameter kks

　　SQL>

　　SQL> create table mac_kks tablespace users nologging as select * from dba_objects;

　　Table created.

　　SQL> insert /*+ append */ into mac_kks select * from mac_kks;

　　77386 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> insert /*+ append */ into mac_kks select * from mac_kks;

　　154772 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> insert /*+ append */ into mac_kks select * from mac_kks;

　　309544 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> insert /*+ append */ into mac_kks select * from mac_kks;

　　619088 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> insert /*+ append */ into mac_kks select * from mac_kks;

　　1238176 rows created.

　　SQL> commit;

　　Commit complete.

　　SQL> oradebug setmypid

　　Statement processed.

　　SQL> oradebug tracefile_name

　　Statement processed.

　　SQL> alter table mac_kks add t2 char(2000) default ‘MACLEAN’;

　　session B:

　　SQL> oradebug setospid 32424

　　Oracle pid: 17, Unix process pid: 32424, image: oracle@vrh8.oracle.com (TNS V1-V3)

　　SQL> oradebug suspend;

　　Statement processed.

　　session C:

　　select * from mac_kks where rownum=1; ==> hang

　　session D:

　　select * from mac_kks where rownum=1; ==> hang

　　session E:

　　SQL> select sid,event from v$session where wait_class!=’Idle’;

　　SID EVENT

　　———- —————————————————————-

　　141 SQL*Net message to client

　　145 library cache lock

　　149 cursor: pin S wait on X

　　159 log buffer space

　　SQL> oradebug setmypid

　　Statement processed.

　　SQL> oradebug dump systemstate 266;

　　Statement processed.

　　SQL> oradebug tracefile_name

　　/s01/admin/G10R25/udump/g10r25_ora_32537.trc

　　Object Names

　　~~~~~~~~~~~~

　　LOCK: handle=a7115ef0

　　Mutex 7fff7abadecf

　　KGX Atomic Operation Log 0x8d88a8d8

　　Mutex 0x954eaff8(145, 0) idn 7fff7abadecf oper EXCL

　　Cursor Pin uid 145 efd 0 whr 1 slp 0

　　opr=3 pso=0x97951af0 flg=0

　　pcs=0x954eaff8 nxt=(nil) flg=35 cld=0 hd=0xa7864b08 par=0x9523a9e0

　　ct=0 hsh=0 unp=(nil) unn=0 hvl=9595c3d8 nhv=1 ses=0xa8416738

　　hep=0x954eb078 flg=80 ld=1 ob=0x95ac6128 ptr=0x8fd90128 fex=0x8fd8f438

　　0x954eaff8(145, 0) ==> sid和 ref count

　　pso ==> parent state object

　　hd=0xa7864b08 ==>cursor 对应的handle address

　　par ==> 父游标的heap 0 pointer

　　ses=0xa8416738 ==》 一般 EXCL是才有 session address v$session.saddr

　　SID=145 对Mutex 0x954eaff8 oper EXCL以X mode Hold 该Mutex, SID=145 在等 SYS.MAC_KKS表的library cache lock，该表被X mode pin和lock，而解析SQL要求以S mode lock该表

　　SID=149 对Mutex 0x954eaff8 申请 oper GET_SHRD， SID=149在等cursor: pin S wait on X

　　KGX Atomic Operation Log 0x8db79798

　　Mutex 0x954eaff8(145, 0) idn 7fff7abadecf oper GET_SHRD

　　Cursor Pin uid 149 efd 0 whr 5 slp 13893

　　opr=2 pso=0x8e6bd518 flg=0

　　pcs=0x954eaff8 nxt=(nil) flg=35 cld=0 hd=0xa7864b08 par=0x9523a9e0

　　ct=0 hsh=0 unp=(nil) unn=0 hvl=9595c3d8 nhv=1 ses=0xa8416738

　　hep=0x954eb078 flg=80 ld=1 ob=0x95ac6128 ptr=0x8fd90128 fex=0x8fd8f438

　　SO: 0xa841bd18, type: 4, owner: 0xa830cf98, flag: INIT/-/-/0×00

　　(session) sid: 149 trans: (nil), creator: 0xa830cf98, flag: (80000041) USR/- BSY/-/-/-/-/-

　　DID: 0001-0019-00000066, short-term DID: 0000-0000-00000000

原文转自：http://blogread.cn/it/article/6410