Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11

thank you again: my test was very simple and I did not use DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE() to persist directives - but I did similar tests before and saw the expected behaviour, so I think adding the test is not that important.

But the statement from Maria Colgan's white paper answers my main question - as you, Mauro, and Chinar already said: the persisting is only done in the cache (and maybe in a corresponding directive). But I fear that this kind of transient information could make performance analysis quite challenging when multiple effects - and some of them almost invisible - have an impact on the optimizer's calculations.

Regards

Martin

Am 11.12.2014 20:17, schrieb Stefan Koehler:
> Hi Martin,
> you may want to provide your test case as it depends on the query and data, if SQL plan directives are applicable or not.
>
>>> I also checked DBA_SQL_PLAN_DIRECTIVES in my test and did not find a corresponding entry
> Did you also flush them to disk by using DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE() before cross-checking?
>
>>> but it is not exactly what I read in "resulting statistics are persistent in the statistics repository".
> That is written in the official online documentation, but the "Understanding Optimizer Statistics with Oracle Database 12c" white paper clearly states
> the following: "In order to minimize the performance impact, the results of the dynamic sampling queries will be persisted in the cache, as dynamic
> statistics, allowing other SQL statements to share these statistics."
>
> 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 20:01 geschrieben:
>>
>>
>> Hi Stefan,
>>
>> thank you for the information and the link (that I visited before asking
>> my question). I forgot to mention that I also checked
>> DBA_SQL_PLAN_DIRECTIVES in my test and did not find a corresponding
>> entry - so obviously the necessary preconditions were not met in this
>> case. But if I understand correctly what plan directives do, I think
>> they store the instruction to gather statistics and not the result of
>> the gathering: of course that's not a bad thing because I would prefer
>> to get a new sampling most of the time - but it is not exactly what I
>> read in "resulting statistics are persistent in the statistics
>> repository". But maybe I focus too much on the wording...
>>
>> Thinking about the components that have an impact on optimizer decisions
>> in 12c (many of them mentioned in your article), it seems to me that it
>> would be difficult task just to create a matrix of overwrites and
>> possible combinations (having ancient stored outlines, sql profiles, sql
>> plan baselines, sql patches, dynamic sampling/dynamic statistics,
>> cardinality feedback/statistics feedback, and finally SQL Plan
>> Directives) - I could imagine quite strange side effects; maybe there
>> are even bugs...
>>
>> Regards
>>
>> Martin Preiss

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