Re: OPTIMIZER_DYNAMIC_SAMPLING=11

Hi Mladen,
OPTIMIZER_DYNAMIC_SAMPLING level 11 was already available with 11.2.0.4.

In your case it would be interesting to know what kind of recursive queries were executed and what kind of child latch (related to the corresponding cache object) plus which "row cache objects" location was responsible for these latch misses.  

By the way OPTIMIZER_DYNAMIC_SAMPLING level 11 may be used "under the hood" even without setting it explicitly to level 11. Christian Antognini has some nice slides about it (slide 10 to 18): http://www.soug.ch/fileadmin/user_upload/SIGs/SIG_150521_Tuning_R/Christian_Antognini_AdaptiveDynamicSampling_trivadis.pdf  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Mladen Gogala <gogala.mladen_at_gmail.com> hat am 5. November 2016 um 20:12 geschrieben:
>
>
> Oracle 12c has a new feature related to optimizer dynamic sampling. The new level 11 is supposed to put the dynamic sampling on auto pilot and make
> the statistics available to other users. However, as soon as this was set, I started noticing processes waiting for row cache latch. This was
> strange, especially having in mind that ASMM was configured. I checked the V$ROWCACHE by using the following query:
>
> So, the latch misses are related to the histograms. The only conceivable mechanism that can be considered a culprit is the new
> OPTIMIZER_DYNAMIC_SAMPLING=11 setting. As soon as that was reset to 2 (11g default), the problem was gone. What are the experiences of other list
> members with ODS in version 12c? I asked the almighty and omniscient Google and came across a very good article by Pythian:
> https://www.pythian.com/blog/performance-problems-with-dynamic-statistics-in-oracle-12c/ Apparently, I wasn't the first to encounter the problem.
> Does anyone on this list have a story to share?
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217

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