Re: High Concurrency Wait

I figured that out, too. That is why I sent my second post, about the "parsing storm".  Such things usually happen when there is forced cursor sharing. There is a contention of a pin that protects a cursor. Another thing that may cause that is setting otpimizer_dynamic_sampling to 11 which slows parsing down. I would advise opening a support case, rather than using latchprofx. Tanel's script will help with discovering a single holder, but it seems that the OP has a massive problem with many SQL statements.

On 11/28/2017 08:11 PM, Sayan Malakshinov wrote:
> 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 <mailto: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 <tel:%28347%29%20321-1217>
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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