Re: High Concurrency Wait

Sanjay,

The event happens when another session is trying to parse the same child cursor as you. First step would be to look at the SQL that hits this event, why is it being parsed so much? v$sql_shared_cursor should give you plenty of help here. If this is happening for multiple SQLs and it's regular then an educated stab in the dark is that there is some parallelism going on, is this the case? The slave processes will be waiting on this event while the main session is parsing the query.

You can look into v$active_session_history to see what the blocking_session was doing, some query like:
select ash.sql_id

      ,substr(sa.sql_text,1,100) sql_text
      ,sa.open_versions
      ,sa.version_count
      ,sa.invalidations
      ,sa.parse_calls
      ,ash.in_hard_parse
      ,ash.in_parse
      ,ash.in_sql_execution
      ,ash.event
      ,blocking_ash.sql_id
      ,blocking_ash.in_hard_parse
      ,blocking_ash.in_parse
      ,blocking_ash.in_sql_execution
      ,blocking_ash.event
      ,count(*)

  on   ash.sample_id = blocking_ash.sample_id
 and   ash.blocking_session = blocking_ash.session_id
 and   ash.blocking_session_serial# = blocking_ash.session_serial#

        ,substr(sa.sql_text,1,100)
        ,sa.open_versions
        ,sa.version_count
        ,sa.invalidations
        ,sa.parse_calls
        ,ash.in_hard_parse
        ,ash.in_parse
        ,ash.in_sql_execution
        ,ash.event
        ,blocking_ash.sql_id
        ,blocking_ash.in_hard_parse
        ,blocking_ash.in_parse
        ,blocking_ash.in_sql_execution
        ,blocking_ash.event;

> 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