OPTIMIZER_DYNAMIC_SAMPLING Level 11
Date: Thu, 11 Dec 2014 18:48:02 +0100
Message-ID: <5489D8D2.9000807_at_gmx.de>
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-lReceived on Thu Dec 11 2014 - 18:48:02 CET