Re: High Concurrency Wait

From: Sayan Malakshinov <>
Date: Wed, 29 Nov 2017 04:11:00 +0300
Message-ID: <>


'cursor: pin S wait on X' is not related to "buffer busy wait" and buffer cache at all.

On Wed, Nov 29, 2017 at 3:43 AM, Mladen Gogala <> wrote:

> Hi Sanjay!
> That used to be known as "buffer busy wait". The problem is in a set of
> blocks within a segment, which is accessed by multiple sessions
> simultaneously. As only a single session can have a X mode pin, the others
> have to wait. In 11.2, Oracle needles and pins (another song reference
> from the 80's for Mark) were completely rewritten to be much cheaper, but
> they are still rather expensive. That type of access swarm usually happens
> on an index blocks, so the first solution is to try reverse ordered index,
> which would scatter those frequently accessed index rows. That is not
> possible if the index is used for range scans, which are not possible with
> reverse ordered indexes. You can also try using result cache. Another trick
> is to switch the database execution mode to threaded, which makes pins and
> mutexes much cheaper, since they are user mode objects.
> You can find the hot blocks by turning on _DB_BLOCK_HOT_TRACKING and
> checking X$KSLHOT table which gets populated when this parameter is turned
> on. Typical result in your situation would show < 10 blocks which are very
> hot. You will then have to map them to the segment to see where the trouble
> is and probably address it from the application side.
> Regards
> On 11/28/2017 03:09 PM, Sanjay Mishra (Redacted sender smishra_97 for
> DMARC) wrote:
> Hi
> Can someone guide as what need to be looked for High wait on "cursor: pin
> S wait on X' happening in on Exadata. I checked with Tanel Ashtop
> and this events comes at more than 75% and even snapper also show the same
> with top event for the same. Running snapper every 5 second are showing
> different SQLID for the event.
> Any link or guidance to check as this been reported only in last few days
> and I checked with dashtop (Tanel Script) and same even is on top in all
> last available 7 days of the ASH history
> Sanjay
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217

Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate

Received on Wed Nov 29 2017 - 02:11:00 CET

Original text of this message