How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (文档 ID 786507.1)
来源:互联网 发布:传奇世界双开辅助软件 编辑:程序博客网 时间:2024/05/01 09:24
In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Oracle Database - Personal Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Database - Standard Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
GOAL
This note helps find the blocking session for mutex related wait event "cursor: pin S wait on X"
To Troubleshoot this event see:
Document 1349387.1 Troubleshooting 'cursor: pin S wait on X' waits
Document 1356828.1 FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
Document 1377446.1 Troubleshooting Performance Issues
SOLUTION
Cursor: pin S wait on X.
A session waits on this event when requesting a mutex for shareable operations related to pins (such as executing a cursor), but the mutex cannot be granted because it is being held exclusively by another session (which is most likely parsing the cursor).
The column P2RAW in v$session or v$session_wait gives the blocking session for wait event cursor: pin S wait on X.
The top bytes of p2raw is the blocker. It is in hex so needs to be converted in decimal.
P2RAW
----------------
0000001F00000000
<SID> <RefCnt>
The top bytes of p2raw is the blocker.
Taking 0000001F (the first 8 bytes) and converting to decimal gives session id 31.
Or simply:
from v$session
where event = 'cursor: pin S wait on X';
P2RAW SID
---------------- ---
0000001F00000000 31
64 bit platforms
8 bytes are used.
Top 4 bytes hold the session id (if the mutex is held X)
Bottom 4 bytes hold the ref count (if the mutex is held S).
32 bit platforms
4 bytes are used.
Top 2 bytes hold the session id (if the mutex is held X)
Bottom 2 bytes hold the ref count (if the mutex is held S).
where event ='cursor: pin S wait on X'
and wait_time = 0
group by p1, p2raw;
- p1 = the mutex Id
This has the same definition as v$mutex_sleep_history.mutex_identifier - p2raw = holding Session Id | Ref Count
The most significant bytes always store the Holding Session Id (Holding SId).
The least significant bytes always store the Ref Count.
The blocking session can be queried to see what it is doing and if anyone is blocking it.
from v$session where SID=31;
As a result of Bug 7568642 BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X" the blocking_session is not populated in 10.2.The bug is fixed in 11g R1.
In 11g, the blocking session can be found directly using the following sql:
from v$session where event ='cursor: pin S wait on X'
SID SERIAL# SQL_ID BLOCKING_SESSION BLOCKING_SESSION_STATUS EVENT
---- ------- ------------- ---------------- ----------------------- ----------
125 8190 3d3pd7g7dwuf6 135 VALID cursor: pin S wait on X
Likely Causes
One of the most likely causes of cursor: pin S wait on X is high parsing time. Therefore the reason for the high parse time should be investigated.
REFERENCES
BUG:7568642 - BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X"
NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.
NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:1377998.1 - Troubleshooting: Waits for Mutex Type Events
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)
- How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (文档 ID 786507.1)
- Determine the Blocking Session for Event: 'cursor: pin S wait on X' [ID 786507.1]【每日一译】--20121107
- How to Find Blocking Session for Mutex Wait Event cursor: pin S wait on X
- oracle wait event:cursor: pin S wait on X
- WAITEVENT: "cursor: pin S wait on X" Reference Note (文档 ID 1298015.1)
- Troubleshooting 'cursor: pin S wait on X' waits. (文档 ID 1349387.1)
- "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason Primarily (文档 ID 1268724.1)
- Troubleshooting 'cursor: pin S wait on X' waits. (文档 ID 1349387.1)
- cursor: pin S wait on X
- cursor pin S wait on X
- 模拟cursor pin S wait on X
- High 'cursor: pin S wait on X' . Cause: Shared Pool/Buffer Cache Resize Activity (文档 ID 742599.1)
- Oracle cursor pin S wait on X 等待事件 说明
- Oracle cursor pin S wait on X 等待事件 说明
- cursor: pin S wait on X影响系统记录
- oracle cursor: pin s wait on x (一)
- oracle cursor: pin s wait on x (二)
- 如何诊断cursor pin s wait on x 系列一
- 利用SecureCRT上传、下载文件(使用sz与rz命令)
- UIView Animations(storyboard)
- Python单例模式的4种实现方法:
- linux 用户和组的管理操作
- 中转站
- How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (文档 ID 786507.1)
- vim编辑
- 【索引】Chapter 1. Algorithm Design_General Problem Solving Techniques_Exercises: Beginner
- PyCharm3.0 中文乱码的解决办法
- 开发板作为USB设备模拟网卡(linux usb 网卡)--gadget&&rndis|cdc
- tab切换页面备份
- 前端技术-参考
- win7 64位 安装ulipad编辑器
- [LeetCode] Pascal's Triangle