Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11
Date: Thu, 11 Dec 2014 20:17:30 +0100 (CET)
Message-ID: <25797194.290886.1418325450348.open-xchange_at_app01.ox.hosteurope.de>
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-lReceived on Thu Dec 11 2014 - 20:17:30 CET