如何診斷cursor pin s wait on x 系列一，cursorpin

如何診斷cursor pin s wait on x 系列一，cursorpin



首先明確一個事情：cursor pin s wait on x 是癥狀（結果），不是根本原因。
mutex related wait event "cursor: pin S wait on X"


一般原因：
》頻繁硬解析（解析時間高. 找出解析時間高的原因很關鍵）----可能性最大
》high version counts
》bug


如何診斷？


1.AWR ADDM 報告


正常效能階段的AWR ADDM 報告 （作為基準比對sql ，loadprofile 等）
非正常效能階段的AWR ADDM 報告 


SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql


2.system state dump


如果AWR 沒有捕獲到異常的sql ，使用 system state dump 可以捕獲 holder ，waiter 進程
(a) Non-Rac
sqlplus "/ as sysdba"


oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
---wait 90 seconds---
oradebug dump systemstate 266
---wait 90 seconds---
oradebug dump systemstate 266
oradebug tracefile_name 
quit


(b) RAC
$ sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 267
oradebug tracefile_name 
quit



3.errorstack
前提：已經確定 blocker 進程 ，使用 errorstack 可以捕獲更多的資訊


$ sqlplus
SQL> oradebug setospid <p.spid from above>
oradebug dump errorstack 3
<< wait 1min>> 
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit




4.如何確定 blocker session？
使用 systemstate dump 對系統開銷（disk space）太大，如果進程很多，則dump 會很大。
不是特別建議使用。可以採取方式：3 
如何確定 blocker session？可以參考：
How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (文檔 ID 786507.1) 



根本原理：
The column P2RAW in v$session or v$session_wait gives the blocking session for wait event cursor: pin S wait on X.


10g：
--for 32bit
select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,4),'XXXX') sid 
     from v$session 
     where event = 'cursor: pin S wait on X'; 
P2RAW               SID 
----------------    --- 
0000001F00000000     00


--for 64bit
select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid 
     from v$session 
     where event = 'cursor: pin S wait on X'; 


P2RAW               SID 
----------------    --- 
0000001F00000000     31 



-----根據上一步中的sid確定阻塞session：
SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT 
     from v$session where SID=31;


-----11g 可以直接找到阻塞會話：
select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT 
     from v$session where event ='cursor: pin S wait on X'


 SID SERIAL# SQL_ID        BLOCKING_SESSION BLOCKING_SESSION_STATUS EVENT  
---- ------- ------------- ---------------- ----------------------- ----------  
 125    8190 lixora-lixora              135 VALID                   cursor: pin S wait on X



5.確定 waiter session
SELECT s.sid, t.sql_text
FROM v$session s, v$sql t
WHERE s.event LIKE '%cursor: pin S wait on X%'

AND t.sql_id = s.sql_id