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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf ;
Of Stefan Knecht
Sent: 19 December 2017 06:39
To: dmarc-noreply@xxxxxxxxxxxxx
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
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 | @zztat_oracle On Tue, Dec 19, 2017 at 4:59 AM, Sanjay
Mishra <dmarc-noreply@xxxxxxxxxxxxx> 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
