Re: High Concurrency Wait
Date: Tue, 28 Nov 2017 19:43:35 -0500
Message-ID: <9f78428e-d86d-7f95-59b3-85533d2a9020_at_gmail.com>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 29 2017 - 01:43:35 CET