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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx>
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
