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@xxxxxxxxx <mailto: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 <tel:%28347%29%20321-1217>
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
