How to diagnose cursor pin s wait on x Series 1, cursorpin

How to diagnose cursor pin s wait on x Series 1, cursorpin



First, clarify one thing: cursor pin s wait on x is the symptom (result), not the root cause.
Mutex related wait event "cursor: pin S wait on X"


General cause:
Frequent and hard parsing (High Resolution time. It is critical to find out the reasons for high resolution time) ---- the most likely
"High version counts
Bug


How to diagnose?


1. awr addm report


Awr addm report in normal performance stage (compare SQL and loadprofile as baseline)
Awr addm report for abnormal performance stage


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


2. system state dump


If no abnormal SQL is captured in AWR, you can use system state dump to capture holder and waiter processes.
(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
Prerequisite: The blocker process has been determined. You can use errorstack to capture more information.


$ Sqlplus
SQL> oradebug setospid <p. spid from above>
Oradebug dump errorstack 3
<Wait 1 min>
Oradebug dump errorstack 3
<Wait 1 min>
Oradebug dump errorstack 3
Exit




4. How to Determine blocker session?
Using systemstate dump is too large for disk space. If there are many processes, dump will be very large.
It is not particularly recommended. Method 3
How to determine blocker session? Refer:
How to Determine the Blocking Session for Event: 'cursor: pin S wait on x' (Documentation ID 786507.1)



Fundamentals:
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), 'xxx') sid
From v $ session
Where event = 'cursor: pin S wait on x ';
P2RAW SID
-------------------
0000001F00000000


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



----- Determine to block the session according to the sid in the previous step:
SQL> select sid, serial #, SQL _ID, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, EVENT
From v $ session where SID = 31;


----- 11g can be used to directly locate the blocking session:
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. Determine the 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