cursor pin S wait on X；
这是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
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
以下是11G测试:
session 1:
--============================
SQL> select sid from v$mystat where rownum=1;
SID
----------
24
--创建测试表
SQL> create table t tablespace users as select *from dba_objects;
表已创建。
--验证系统是否使用mutex机制
SQL> SELECT nam.ksppinm NAME, val.ksppstvl VALUE
FROM x$ksppi nam, x$ksppsv val
WHERE nam.indx = val.indx
AND nam.ksppinm LIKE '%mutex%'
ORDER BY 1;
2 3 4 5
NAME VALUE
------------------------------ ----------
_kgl_mutex_wait_time 0
11G 默认值为0
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
----------
32
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 /
--================================
查看ASH监控:
BLOCKING_SESSION
12 2009070 24 14-3月 -01 10.45.34.268 下午 9kvh5ub3p290k cursor: pin S wait on X 32 select object_id from t where object_id=13
SELECT sid,
SUBSTR (event, 1, 30),
p1,
TO_CHAR(p1, 'xxxxxxxx') p1_16,
--P1RAW P1_16,
p2,
p3
FROM v$session_wait
WHERE sid in (32,12)
SID SUBSTR(EVENT,1,30) P1 P1_16 P2 P3
---------- ------------------------------------------------------------ ---------- --------- ---------- ----------
12 rdbms ipc message 300 12c 0 0
32 cursor: pin S wait on X 272866798 10439dee 1572864 327680
select b.*, sq.sql_text
from v$session se,
v$sql sq,
(select a.*, s.sql_text
from v$sql s,
(select sid,
event,
wait_class,
p1,
p2raw,
to_number(substr(p2raw, 1, 4), 'xxxx') sid_hold_mutex_x
from v$session_wait
where event like 'cursor%') a
where s.HASH_VALUE = a.p1) b
where se.sid = b.sid
and se.sql_hash_value = sq.hash_value;
SID EVENT WAIT_CLASS P1 P2RAW SID_HOLD_MUTEX_X SQL_TEXT SQL_TEXT
---------- -------------------- ---------- ---------- ---------------- ---------------- ---------- ----------
32 cursor: pin S wait o Concurrenc 3952272319 0000000000180000 0 select obj select obj
n X y ect_id fro ect_id fro
m t where m t where
object_id= object_id=
89 89