Re: High Concurrency Wait

Mladen,

'cursor: pin S wait on X' is not related to "buffer busy wait" and buffer cache at all.
https://docs.oracle.com/database/121/REFRN/GUID-6230F000-F5E2-4589-BD2E-E2B0686D901D.htm#REFRN00525

On Wed, Nov 29, 2017 at 3:43 AM, Mladen Gogala <gogala.mladen_at_gmail.com> 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 12.1.0.2 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
>
> TIA
> Sanjay
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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