Wednesday, January 10, 2018

What is "Automatic Reoptimization" introduced in Oracle 12cR1?

Some definitions here, mostly taken directly from Oracls documentation, or quoted from Tim Hall's www.oracle-base.com

Automatic Reoptimization


Automatic Reoptimization is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement.

Automatic reoptimization takes two forms: Statistics Feedback and Performance Feedback.

Statistics feedback


Statistics Feedback (formerly known as cardinality feedback and first introduced in Oracle 11gR2) is a type of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates.
  • At the end of an execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. 
  • If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use and the statement is marked as "reoptimizable". 
  • When the query executes again, the optimizer uses the corrected cardinality estimates instead of its usual estimates, allowing a better plan to be determined
  • Statistics Feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool.


Performance Feedback


This form of automatic reoptimization helps improve the degree of parallelism automatically chosen for repeated SQL statements when PARALLEL_DEGREE_POLICY is set to ADAPTIVE.

  • At the end of an initial execution, the optimizer compares the degree of parallelism chosen by the optimizer with the degree of parallelism computed based on the performance statistics (for example, the CPU time) gathered during the actual execution of the statement
  • If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution  statistics as feedback. This feedback helps better compute the degree of parallelism for subsequent executions.

Note that even if PARALLEL_DEGREE_POLICY is not set to ADAPTIVE, statistics feedback may influence the degree of parallelism chosen for a statement.

In short, to remember it more easily:

  • statistics feedback deals with incorrect cardinality
  • performance feedback deals with incorrect parallel execution


Sources:

No comments:

Post a Comment