Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 11 Dec 2014 19:33:03 +0100 (CET)
Message-ID: <1855163984.290193.1418322784018.open-xchange_at_app01.ox.hosteurope.de>

Hi Martin,
the dynamic sampling results are stored in the shared pool / library cache (= dynamic statistics) and can be shared with other SQL statements that benefit from it as well. I have not done any further geek researching of it yet (e.g. dumping shared pool and looking into heap details, etc.). However the dynamic statistics are gone as well as you have executed an "alter system flush shared_pool" in between. They become persistent in the "statistics repository" as SQL plan directives, but this needs further steps to kick in.

You may want to read my blog post about this: http://tinyurl.com/q7qomja

Best Regards
Stefan Koehler

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

> Martin Preiss <mtnpreiss_at_gmx.de> hat am 11. Dezember 2014 um 18:48 geschrieben:
>
> Dear members of the list,
>
> the documentation - http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL453 - tells us that in 12.1.0.2 level 11 of the parameter
> OPTIMIZER_DYNAMIC_SAMPLING instructs the optimizer to "use dynamic statistics automatically when the optimizer deems it necessary. The resulting
> statistics are persistent in the statistics repository, making them available to other queries."
>
> My question is: is there an interface to check the persisted information? Doing some simple checks with event 10046, it seemed to me that the
> following steps did not result in an access of persisted statistics for the second query execution:
> * alter session set OPTIMIZER_DYNAMIC_SAMPLING = 11;
> * exec dbms_monitor.session_trace_enable();
> * execute a simple query with skewed data distribution and without histograms (resulting in dynamic sampling and a fitting plan)
> * alter system flush shared_pool;
> * execute the simple query again (resulting in dynamic sampling again)
> * exec dbms_monitor.session_trace_disable()
>
> The resulting trace file shows the execution of the same /* DS_SVC */ queries preceeding the execution of my test query (some of them could use the
> result cache): so I don't see an access on persisted information from the "statistics repository" (whatever that may be).
>
> My impression is that the documentation is not very verbose in describing the details - so I hope that you can shed some light on the affair.
>
> Regards
>
> Martin Preiss

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

Received on Thu Dec 11 2014 - 19:33:03 CET