转自：http://warehouse.itpub.net/post/777/493962
这是10.2版本提出的mutex(互斥)机制用来解决library cache bin latch争夺问题引入的新事件,是否使用这种机制受到隐含参数_kks_use_mutex_pin的限制,从10.2.0.2开始该参数default为true,使用这种机制oracle是为了解决library cache bin latch的串行使用问题,但是mutex貌似还不是很稳定,在很多系统中会出现cursor: pin S wait on X等待事件,这个事件和mutex的使用有关,最近一客户受到cursor: pin S wait on X等待事件的困扰,出现cursor: pin S wait on X等待事件时通常等待比较严重,系统会出现hang,这个事件的出现受到很多因素的影响:
在高并发的情况下:
1.sga自动管理,sga的频繁扩展和收缩
2.过渡硬解析,造成library cache中的cursor object被频繁的reload
3.bug
--一下是对cursor: pin S wait on X的模拟
doc描述
cursor: pin S wait on X
A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.
Wait Time: Microseconds
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
--==============
session 1:
--============================
SQL> select sid from v$mystat where rownum=1;
SID
----------
145
--创建测试表
SQL> create table t tablespace users as select *from dba_objects;
表已创建。
--验证系统是否使用mutex机制
SQL> SELECT nam.ksppinm NAME, val.ksppstvl VALUE
2 FROM x$ksppi nam, x$ksppsv val
3 WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%mutex%'
4 ORDER BY 1;
NAME VALUE
------------------------------ ------------------------------
_kks_use_mutex_pin TRUE
SQL>
SQL> declare
2 v_string varchar2(100) := 'alter system flush shared_pool';
3 msql varchar2(200);
4 begin
5 loop
6 execute immediate v_string;
7 for i in 1 .. 100 loop
8 msql:='select object_id from t where object_id='||i;
9 execute immediate msql;
10 end loop;
11 end loop;
12 end;
13 /
--==============================
session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
147
SQL> declare
2 v_string varchar2(100) := 'alter system flush shared_pool';
3 msql varchar2(200);
4 begin
5 loop
6 execute immediate v_string;
7 for i in 1 .. 100 loop
8 msql:='select object_id from t where object_id='||i;
9 execute immediate msql;
10 end loop;
11 end loop;
12 end;
13 /
--================================
session 3:(监控)
SQL> select b.*, sq.sql_text
2 from v$session se , v$sql sq ,
3 (
4 select a.*,s.sql_text
5 from v$sql s ,
6 (
7 select sid,event,wait_class,p1,p2raw,to_number(substr(p2raw,1,4),'xxxx') si
d_hold_mutex_x from v$session_wait where event like 'cursor%'
8 ) a
9 where s.HASH_VALUE=a.p1
10 ) b
11 where se.sid=b.sid and se.sql_hash_value=sq.hash_value
12 ;
未选定行
SQL> /
未选定行
SQL> /
SID EVENT WAIT_CLASS P1
---------- ------------------------------ -------------------- ----------
P2RAW SID_HOLD_MUTEX_X SQL_TEXT
---------- ---------------- ---------------------------------------------
SQL_TEXT
---------------------------------------------
145 cursor: pin S wait on X Concurrency 3919826214
00930000 147 select object_id from t where object_id=32
select object_id from t where object_id=32
SQL> /
SID EVENT WAIT_CLASS P1
---------- ------------------------------ -------------------- ----------
P2RAW SID_HOLD_MUTEX_X SQL_TEXT
---------- ---------------- ---------------------------------------------
SQL_TEXT
---------------------------------------------
147 cursor: pin S wait on X Concurrency 2443024442
00910000 145 select object_id from t where object_id=84
select object_id from t where object_id=84
SQL> /
未选定行
SQL> /
SID EVENT WAIT_CLASS P1
---------- ------------------------------ -------------------- ----------
P2RAW SID_HOLD_MUTEX_X SQL_TEXT
---------- ---------------- ---------------------------------------------
SQL_TEXT
---------------------------------------------
145 cursor: pin S wait on X Concurrency 3592317462
00930000 147 select object_id from t where object_id=14
select object_id from t where object_id=14
SQL> /
未选定行
SQL> /
SID EVENT WAIT_CLASS P1
---------- ------------------------------ -------------------- ----------
P2RAW SID_HOLD_MUTEX_X SQL_TEXT
---------- ---------------- ---------------------------------------------
SQL_TEXT
---------------------------------------------
147 cursor: pin S wait on X Concurrency 3302564824
00910000 145 select object_id from t where object_id=53
select object_id from t where object_id=53
SQL>
--通过监控发现145,147session在执行相同的sql,他们在相同的cursor object上交互请求a shared mutex pin或者 an exclusive mutex pin 从而造成等待
--========================
--监视sql reae区的cursor object reload情况,如果是由第1,2中情况造成的等待,那么reload会比较严重
SQL> select namespace ,reloads from v$librarycache;
NAMESPACE RELOADS
------------------------------ ----------
SQL AREA 54485
TABLE/PROCEDURE 5364
BODY 266
TRIGGER 18
INDEX 22
CLUSTER 6
OBJECT 0
PIPE 0
JAVA SOURCE 0
JAVA RESOURCE 0
JAVA DATA 0
已选择11行。
SQL>
--==============================
--监视parse情况
SQL> col name format a40
SQL> select s.sid, s.serial#,b.name,a.value
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic# and s.sid=a.sid
4 and b.name like '%parse%'
5 and s.sid in (145,147);
SID SERIAL# NAME VALUE
---------- ---------- ---------------------------------------- ----------
145 43 parse time cpu 96096
145 43 parse time elapsed 224207
145 43 parse count (total) 536513
145 43 parse count (hard) 165417
145 43 parse count (failures) 0
147 184 parse time cpu 108948
147 184 parse time elapsed 225237
147 184 parse count (total) 560590
147 184 parse count (hard) 190633
147 184 parse count (failures) 0
已选择10行。
SQL>
--=====================
--和mutex相关的view有2个,也可以通过这2个视图来查看mutex的使用情况
SQL> desc v$mutex_sleep
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
MUTEX_TYPE VARCHAR2(32)
LOCATION VARCHAR2(40)
SLEEPS NUMBER
WAIT_TIME NUMBER
SQL> col location format a30
SQL> col mutex_type format a20
SQL> select * from v$mutex_sleep;
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
-------------------- ------------------------------ ---------- ----------
Cursor Parent kkspsc0 [KKSPRTLOC26] 894 6216485
Cursor Parent kksfbc [KKSPRTLOC2] 33 179918
Cursor Parent kksfbc [KKSPRTLOC1] 103 608615
Cursor Pin kksSetBindType [KKSCHLPIN4] 1 25479
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
-------------------- ------------------------------ ---------- ----------
Cursor Pin kksSetBindType [KKSCHLPIN3] 1 12392
Cursor Pin kkslce [KKSCHLPIN2] 47486 1703401018
已选择6行。
SQL>
SQL> desc v$mutex_sleep_history
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SLEEP_TIMESTAMP TIMESTAMP(6)
MUTEX_TYPE VARCHAR2(32)
GETS NUMBER
SLEEPS NUMBER
REQUESTING_SESSION NUMBER
BLOCKING_SESSION NUMBER
LOCATION VARCHAR2(40)
MUTEX_VALUE RAW(4)
P1 NUMBER
P1RAW RAW(4)
P2 NUMBER
P3 NUMBER
P4 NUMBER
P5 VARCHAR2(64)
SQL> select count(*) from v$mutex_sleep_history;
COUNT(*)
----------
434
SQL>
SQL> col event format a40
SQL> select a.* from
2 (
3 select event,total_waits,total_timeouts from v$system_event order by total_
waits desc
4 ) a
5 where rownum6 ;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS
---------------------------------------- ----------- --------------
cursor: pin S wait on X 56003 56001
rdbms ipc message 15754 14761
db file sequential read 4926 0
library cache load lock 3054 0
latch: library cache 2424 0
SQL>