RE: query performance following 12c upgrade

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 23 Sep 2015 16:34:26 +0200 (CEST)
Message-ID: <928074502.50796.1443018866235.JavaMail.open-xchange_at_app10.ox.hosteurope.de>



Hi Jonathan,

> you would find that the rest of your 13 seconds is Oracle running dynamic sampling queries against the data so that it can decide on the best
> execution plan.

DS has a time limit and based on the provided data the time limit would be max. 1 or 10 seconds as the query itself runs only a few ms. Currently we can not say which limit counts as we do not know if the query was present in the cursor cache or AWR. However in none of these cases we would sum up to 13 seconds afaik.

Christian Antognini described the algorithm in his presentation - slide 14 to 18: http://www.soug.ch/fileadmin/user_upload/SIGs/SIG_150521_Tuning_R/Christian_Antognini_AdaptiveDynamicSampling_trivadis.pdf

I would still count on latching ;-)

P.S.: In addition level 11 is used with 12.1.0.2 regardless what the note section in DBMS_XPLAN says (slide 12 of Christian's presentation)  

Best Regards
Stefan Koehler

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

> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> hat am 23. September 2015 um 16:02 geschrieben:
>
> I think that if you enabled SQL trace you would find that the rest of your 13 seconds is Oracle running dynamic sampling queries against the data so
> that it can decide on the best execution plan. It might also be spending some time querying the data dictionary to find out what the SQL Directives
> for this table were; that would be a little unusual but I think I saw a bug note on MoS recently that said something about one table ending up with
> literally thousands of directives associated with it.
>
> All the automatic dynamic stats and dynamic sampling switches off when you set the database back to 11.2.0.4.
> I can't remember the parameter names, but there are a couple of parameters you could use to disable some of the adaptive and dynamic stuff if this
> is causing a system-wide problem.
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 23 2015 - 16:34:26 CEST

Original text of this message