Re: Adaptive Query Optimization

> When does Adaptive Statistics (Cardinality Feedback) come into play in 12c in this context? Won't a plan based on bad cardinality estimates have
> already been "fixed" at run-time during the first execution by the Adaptive Plan functionality?

Not necessarily as adaptive plans are for joins (switching between hash and nested loop joins) and PX distribution methods. However there are a lot of other culprits like missing statistics, inaccurate statistics, or complex predicates that influences all the other access methods (e.g. table vs. index access) or join orders. There is a section called "Automatic Reoptimization" in the Oracle documentation that explains this in a little bit more detail: https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#BABCEBCA

"Whereas adaptive plans help decide between multiple subplans, they are not feasible for all kinds of plan changes. For example, a query with an inefficient join order might perform suboptimally, but adaptive plans do not support adapting the join order during execution. In these cases, the optimizer considers automatic reoptimization. In contrast to adaptive plans, automatic reoptimization changes a plan on subsequent executions after the initial execution."

> When would it need to be subsequently re-optimized using Adaptive Statistics?

I think you mean statistics feedback (formerly called cardinality feedback) as adaptive statistics include much more than the formerly called cardinality feedback feature. A general statement is also provided in the "Automatic Reoptimization" documentation section:

"A form of reoptimization known as statistics feedback (formerly known as cardinality feedback) automatically improves plans for repeated queries that have cardinality misestimates. The optimizer can estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates.

The basic process of reoptimization using statistics feedback is as follows: During the first execution of a SQL statement, the optimizer generates an execution plan. The optimizer may enable monitoring for statistics feedback for the shared SQL area in the following cases:

- Tables with no statistics
- Multiple conjunctive or disjunctive filter predicates on a table
-Predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates

You may find my blog post some kind of useful as it explains some terms and it gets even more complex as there are other new influencing functionalities as well: http://tinyurl.com/q7qomja

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Charlotte Hammond <dmarc-noreply_at_freelists.org> hat am 23. März 2015 um 17:54 geschrieben:
>
> Hi All,
> I'm reading about Adaptive Query Optimization in 12c and I'm a bit confused about the interplay of Adaptive Plans and Adaptive Statistics. I
> would greatly appreciate any explanation on this....
>
> When does Adaptive Statistics (Cardinality Feedback) come into play in 12c in this context? Won't a plan based on bad cardinality estimates have
> already been "fixed" at run-time during the first execution by the Adaptive Plan functionality? When would it need to be subsequently re-optimized
> using Adaptive Statistics?
>
> Many thanks!
> Charlotte

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