Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11
- From: Stefan Koehler <contact@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, mtnpreiss@xxxxxx
- Date: Thu, 11 Dec 2014 19:33:03 +0100 (CET)
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: @OracleSK
> Martin Preiss <mtnpreiss@xxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
