ITPub博客

首页 > 应用开发 > IT综合 > cursor: pin S wait on X等待事件模拟

cursor: pin S wait on X等待事件模拟

原创 IT综合 作者:warehouse 时间:2009-11-22 16:53:30 0 删除 编辑

这是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>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1028941/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 俺稀罕这几句话
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098436