Maybe a bug. When it comes to dynamic sampling, there is more than one:
Bug 17632286 - ORA-600 [qksdsUpdTabStatsCbk:0] when dynamic_sampling=11
(Doc ID 17632286.8)
Bug 19631234 Suboptimal execution plan for Dynamic Sampling Queries
Bug 17760686 - Bad Cardinality estimation with dynamic sampling (Doc ID
17760686.8)
There are also bugs with "cursor: pin S on X":
Bug 23003919 Excessive 'library cache lock' & 'cursor: pin s wait on x'
waits by Query with binds, PARALLEL hint and session parallel execution
disabled
Bug 21153142 Row cache lock self deadlock accessing seed PDB
Bug 21834574 Mutex contention and increased hard parse time due to ILM
(segment-access tracking) checking with partitioned objects
The easiest thing in the world is to blame the Oracle for bug, but that
usually doesn't solve the problem. My experience is that Oracle is
usually able to provide some kind of workaround if you have enough
patience to cut through the first line of their tech support. Oracle
RDBMS introduced so many major features in so short time that bugs are
simply inevitable. Here is a little music, so that your times go by in
a more pleasant way:
https://www.youtube.com/watch?v=Mk3qkQROb_k
On 11/28/2017 08:22 PM, Sayan Malakshinov wrote:
BAAG!
On Wed, Nov 29, 2017 at 4:17 AM, Mladen Gogala <gogala.mladen@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> wrote:
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.
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
