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

转自: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>

时间: 2024-11-13 07:29:23

cursor: pin S wait on X等待事件模拟(转)的相关文章

[20130628]关于cursor pin S以及cursor pin S wait on X的相关问题.txt

[20130628]关于cursor pin S以及cursor pin S wait on X的相关问题.txt 生产系统最近一段时间总是出现cursor pin S wait on X,以前从来没有遇到,今天抽空看了一下.了解关于cursor pin S以及cursor pin S wait on X的相关问题,我看了一些链接: http://www.pythian.com/blog/cursor-pin-s-wait-on-x-in-the-top-5-wait-events/http:/

【MOS】常见问题cursor library cache类型的等待事件

[MOS]常见问题:'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'类型的等待事件 (文档 ID 1525791.1) 文档内容 用途 问题和答案   什么是 'cursor: ' 等待事件?   最常见的等待事件是什么?   等待事件最常见的原因是什么?   如何避免这些等待事件?   可以在什么位置找到原因诊断以及关于这些等待事件的更多信息?   有用参考 参考 适用于: Oracle Database - Enterp

【等待事件】等待事件系列(1)--User I/O类型

[等待事件]等待事件系列(1)--User I/O类型 1  BLOG文档结构图     2  前言部分   2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ①  等待事件系列(1)--User I/O类型     Tips: ① 本文在ITpub(http://blog.itpub.net/26736162).博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaim

[20130228]等待事件library cache pin的快速定位与解决.txt

[20130228]等待事件library cache pin的快速定位与解决.txt 前几天管理的服务器出现library cache pin,当时解决有点乱了阵脚,正好下午空闲做一个例子来定位library cache pin事件以及解决方法,另外我也看许多blog,感觉定位太复杂,不合适快速解决问题: 1.环境以及问题再现: SQL> select * from v$version where rownumBANNER------------------------------------

怎样诊断过去某个时间段的等待事件原因

1.查看某个等待事件的阻塞会话 select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash    where sample_time>=to_timestamp('2013-06-24 12:25:00','yyyy-mm-dd hh24:mi:ss')    and sample_time<=to_timestamp('2013-06-24 12:35:00','yyyy-mm-dd

Oracle Mutex 等待事件

Oracle Mutex 等待事件之: cursor pin S pin S 等待事件说明,当一个会话(session)试图去更新一个共享的 Mutex Pin,同时已经有其他会话正在更新(updating)同一个 Shared Mutex Pin.注意,Pin S 的Mutex位于子游标上,是动态创建的,无法从等待事件中获得,但是可以在竞争出现的情况下,通过观察 x$mutex_sleep_history 来获得. 文档说明如下: Oracle Mutex 等待事件之: cursor pin

oracle等待事件3——高速缓冲内enq锁

6. enq:TC-contention 在手动执行检查点操作中,一部分需要获得TC锁(thread checkpointlock 或 tablespace checkpointlock )在获得TC锁过程中,若发生争用,则需要等待enq:TC-contention 事件.事实上获得TC锁的过程稍微复杂. 1) 服务器进程首先以X模式获得TC锁 2) 服务器进程将已获得的TC锁变更为SSX模式.同时,CKPT进程以SS模式获得该锁.CKPT获得锁后执行检查点操作. 3) 服务器欲重新以X模式获得

Oracle OWI 等待事件视图(v$session_wait/v$session_event/v$system_event)

    通常情况下,用户提交一条SQL语句,总会存在这样或那样的等待事件.也就是说由于所需资源被占用导致进程不得不处于等待状态.Oracle为我们提供了获取这些等待事件的可用视图.根据这些视图可以得知哪些事件导致该SQL语句效率低下而采取相应的修改或调整.本文基于Oracle 10g描述了如何通过视图v$session_wait,v$session_event,以及v$system_event去获取等待事件的相关信息.   1.等待事件相关参数  timed_statistics      指定

oracle等待事件1——高速缓冲内cbc latch

恩..从今天起,木木同学要认真整理一下oracle中常见的等待事件,通过这部分的学习,希望自己能对oracle内部的结构能有一个更清晰的认识,有兴趣的童鞋一起来哇... 1.latch:cache buffers chains 从oracle 9i开始,以只读为目的的查询chains时,可以将cache buffers chains锁存器以shared模式共享,因此有助于减少争用. (我们需要注意,若能共享cache buffer chains 锁存器,理论上理论上不应该发生同时执行select