From: Martin Preiss <mtnpreiss_at_gmx.de>
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 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.


Martin Preiss

Received on Thu Dec 11 2014 - 18:48:02 CET

Original text of this message