Re: Cusror: Pin S wait in X

Thanks Carlos, Paul
Found the issue as there is sql profile  to use index and Index was in Unusable state causing the issue. Rebuilt the index and all was back. Thanks for providing some interesting details to troubleshoot Thanks AgainSanjay

    On Tuesday, December 19, 2017 9:52 AM, Carlos Sierra <carlos.sierra.usa_at_gmail.com> wrote:  

 Sanjay,
Tanel’s snapper.sql script can help you get more insight on your issue, or you can look in ASH (I attached a couple of scripts I use for that).  I recently had a “cursor: Pin S wait on X” on a 12c DB executing some query over a hundred times per second, and gathering stats often with invalidate. Plan was serial, but at times I would get this event for a few seconds, having a set of sessions trying to parse the same SQL. On my case, there was also a HVC/ACS bug making things worse. But every case is different. You have to find the specifics of yours by analyzing the metadata that is available. Cheers — Carlos

On Dec 19, 2017, at 04:01, Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> wrote: My understanding is that this is caused by parsing the same statement multiple times, i.e. cursors not sharing. You need to try and figure out the reason. I just wrote a blog post about this – see http://people.ds.cam.ac.uk/psh35/blog/post/parsing/. I found Carlos Sierras script (linked from the post) really helpful as you see.  Check that you aren’t encountering bug 20476175  if you are 12CR1.  I have just ventured into blogging by the way, and would welcome any constructive criticism. Please email me directly with that.  I hope this helps.  Paul Houghton      From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Knecht Sent: 19 December 2017 06:39
To: dmarc-noreply_at_freelists.org
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: Re: Cusror: Pin S wait in X  More details are needed to give you any kind of specific answer:  - What Oracle version exactly?- What are these sessions running? (Query + Plan)- Does the query run in parallel?- Does the query use bind variables?- You can also look in x$mutex_sleep to see the most active mutexes (it's likely Cursor Pin but might be something else on the off chance). Snap it a few times and compare to see what's actively being used right now.  Stefan    -- //zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat | _at_zztat_oracle  On Tue, Dec 19, 2017 at 4:59 AM, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote: Can some one suggest what can be done to stop these wait as it coming as topmost event for multiple SID ? I tried to following 786507.1 doc to identify the session but if I kill one session another one comes up. I even bounced the Instance one by one but this still continues. Any suggestion as else had to open Oracle ticket due to slowness been reported. Ashtop shows all top 20 listed process with same Waiteven and with one same SQL id  TIASanjay    

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