Re: High Concurrency Wait

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_at_gmail.com <mailto:gogala.mladen_at_gmail.com>> 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

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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