RE: Cusror: Pin S wait in X

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 19 Dec 2017 13:34:56 -0500
Message-ID: <0d7701d378f8$12e9c890$38bd59b0$_at_rsiz.com>



Carlos mentioned serial. This triggers a memory of (I think) this event going wild on parallel queries when the children for some reason don’t match the parent’s hash and hilarity ensues.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Carlos Sierra Sent: Tuesday, December 19, 2017 9:51 AM To: dmarc-noreply_at_freelists.org
Cc: Oracle-L Freelists; Paul.Houghton_at_uis.cam.ac.uk Subject: Re: Cusror: Pin S wait in X  

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  

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 19 2017 - 19:34:56 CET

Original text of this message