Re: High Concurrency Wait

Sanjay,

Troubleshooting 'cursor: pin S wait on X' waits. (Doc ID 1349387.1) https://support.oracle.com/epmos/faces/DocContentDisplay?id=1349387.1 Have you checked this document?
In addition to the info about troubleshooting this issue, you can find there a couple of public bugs on 12.1.0.2: Database Hang on 12c with 'Cursor: Pin S wait on X', 'Row Cache Lock' and 'Library Cache Load Lock' (Doc ID 2127483.1) High Wait Time for 'cursor: pin S wait on X' Event After Upgrade (Doc ID 1949691.1)

On Wed, Nov 29, 2017 at 4:13 AM, Andy Sayer <andysayer_at_gmail.com> wrote:

> 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(*)
> from v$active_session_history ash
> left join v$active_session_history blocking_ash
> 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#
> left join v$sqlarea sa
> on ash.sql_id = sa.sql_id
> where ash.event = 'cursor: pin S wait on X'
> group by ash.sql_id
> ,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;
>
> Might give you a starting point, you'll probably want to do some more
> drilling.
>
> Hope this helps,
> Andrew
>
>
> On 29 November 2017 at 01:11, Sayan Malakshinov <xt.and.r_at_gmail.com>
> 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>
>> 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
>>
>
>

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

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