Re: Understanding "cursor: pin S wait on X"

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 12 Apr 2013 15:06:19 +0300
Message-ID: <CAMHX9JL062Qre67nEd2FX_C_HGJwWJHJGuSDGCihaTcqeP8G8A_at_mail.gmail.com>

Marco,
So what was the STATE column of this hung session - WAITING or WAITED%. If it was waited, then your session was actually on CPU and the SQL*Net message from client wait event was just the last wait event before the session started burning CPU. Running TOP on the SPID would have shown this too... and in case it actually was on CPU, pstack would have been the next thing to run, to show where in the Oracle kernel code the process was spinning.

The demo script I pasted here earlier pretty much recreates this kind of a situation. It could have been that parsing / optimizing / loading of some widely used cursor got into a spin, the cursor was kept pinned and now more and more sessions started by the connection pool just ended up waiting for the pin until the original parse finishes...

-- 
*Tanel Poder*
Enkitec (The Exadata Experts)
Training <http://blog.tanelpoder.com/seminar/> |
Troubleshooting<http://blog.tanelpoder.com/>
 | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923>
 | Voicee App <http://voic.ee/>



On Fri, Apr 12, 2013 at 1:46 PM, Marko Sutic <marko.sutic_at_gmail.com> wrote:



> We have experienced problems with "cursor: pin S wait on X" on 11.1.0.7

> version.

> Every few weeks session hanged with status "ACTIVE" and "SQL*Net message

> from client" wait event.

> When another session tried to execute the same sql statement it finished

> waiting for "cursor: pin S wait on X".

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 12 2013 - 14:06:19 CEST