Re: High Concurrency Wait

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