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

Source: Internet
Author: User

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






Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.