OPTIMIZER_DYNAMIC_SAMPLING Level 11
- From: Martin Preiss <mtnpreiss@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 11 Dec 2014 18:48:02 +0100
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
Other related posts:
