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

[@more@]

--一下是對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 rownum<6
6 ;

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>