8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Automatic Reoptimization in Oracle Database 12c Release 1 (12.1)
Adaptive Query Optimization in Oracle Database 12c is made up of a number of different features. Functionality like Adaptive Plans allow modification of the execution plan at runtime, but these modifications do not allow the join order of the plan to change. Automatic Reoptimization is based on learning lessons from a previous execution and feeding that information back to the optimizer, so it can make a better decision the next time round.
Control of the adaptive optimizations has altered in 12.2 and the change is available in 12.1 with the correct patches. See here.
- Statistics Feedback (Cardinality Feedback)
- Performance Feedback
- Statistics Feedback and SQL Plan Directives (How They Interact)
Related articles.
- Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2)
- Adaptive Plans in Oracle Database 12c Release 1 (12.1)
- Dynamic Statistics in Oracle Database 12c Release 1 (12.1)
- SQL Plan Directives in Oracle Database 12c Release 1 (12.1)
Statistics Feedback (Cardinality Feedback)
Cardinality feedback was introduced in Oracle Database 11g Release 2. When the optimizer generates an execution plan the presence of missing statistics, stale statistics, complex predicates or complex operators may trigger the optimizer to monitor the cardinality of operations in the plan. Once the execution is complete, if there is a significant difference between the estimated and actual cardinalities, the actual cardinalities are stored in the SGA for later use and the statement is marked as reoptimizable. On next execution the statement is reoptimized using the stored cardinalities, allowing a better plan to be determined. Cardinality feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool. In Oracle Database 12c, cardinality feedback has been renamed to statistics feedback.
Statistics feedback information is stored in the SGA as OPT_ESTIMATE
hints in the V$SQL_REOPTIMIZATION_HINTS
view. Both the view and the OPT_ESTIMATE
hint are undocumented.
The following code creates a pipelined table function to allow us to demonstrate statistics feedback.
CONN test/test@pdb1 -- Create the types to support the table function. DROP TYPE t_tf_tab; DROP TYPE t_tf_row; CREATE TYPE t_tf_row AS OBJECT ( id NUMBER, description VARCHAR2(50) ); / CREATE TYPE t_tf_tab IS TABLE OF t_tf_row; / -- Build the table function itself. CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS BEGIN FOR i IN 1 .. p_rows LOOP PIPE ROW (t_tf_row(i, 'Description for ' || i)); END LOOP; RETURN; END; /
We know the optimizer always estimates the cardinality of pipelined table functions based on the database block size, so we can expect an incorrect estimate of the cardinality of a query against the pipelined table function. The following query returns 10 rows, but the optimizer estimates 8168 rows. The GATHER_PLAN_STATISTICS
hint is used to allow us to display the expected and actual cardinalities.
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(get_tab_ptf(10)); SET LINESIZE 200 PAGESIZE 100 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 0ktmsgvczysxy, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(get_tab_ptf(10)) Plan hash value: 822655197 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF | 1 | 8168 | 10 |00:00:00.01 | ------------------------------------------------------------------------------------------------- SQL>
Checking the IS_REOPTIMIZABLE
column of the V$SQL
view shows the optimizer has detected the incorrect cardinality estimates and has marked the statement to be reoptimized.
COLUMN sql_text FORMAT A50 COLUMN is_reoptimizable FORMAT A16 SELECT sql_text, is_reoptimizable FROM v$sql WHERE sql_text LIKE '%get_tab_ptf%' AND sql_text NOT LIKE '%v$sql%'; SQL_TEXT IS_REOPTIMIZABLE -------------------------------------------------- ---------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM Y TABLE(get_tab_ptf(10)) SQL>
If we run the statement again, we can see a more accurate cardinality estimate and a note telling us statistics feedback was used. Notice also the change in the value of the child number.
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(get_tab_ptf(10)); SET LINESIZE 200 PAGESIZE 100 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 0ktmsgvczysxy, child number 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM TABLE(get_tab_ptf(10)) Plan hash value: 822655197 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF | 1 | 20 | 10 |00:00:00.01 | ------------------------------------------------------------------------------------------------- Note ----- - statistics feedback used for this statement SQL>
In 11gR2 the note would read, "cardinality feedback used for this statement".
The documentation suggests the detection of cardinality misestimates will also result in the production of SQL plan directives (see last paragraph of point 1), which myself and others have misunderstood as meaning statistics feedback is persisted in the SQL plan directive. This does not appear to be the case and MOS Doc ID 1344937.1 confirms the statistics feedback information is lost when the statement is aged out of the shared pool or the instance is restarted. We can check for the presence of SQL plan directives as follows.
CONN sys@pdb1 AS SYSDBA EXEC DBMS_SPD.flush_sql_plan_directive; SET LINESIZE 200 COLUMN dir_id FORMAT A20 COLUMN owner FORMAT A10 COLUMN object_name FORMAT A10 COLUMN col_name FORMAT A10 SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, o.subobject_name col_name, o.object_type, d.type, d.state, d.reason FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o WHERE d.directive_id=o.directive_id AND o.owner = 'TEST' ORDER BY 1,2,3,4,5; no rows selected SQL>
As we can see, in this case no SQL plan directives were created. We will discuss this point a little more later.
Performance Feedback
Oracle 11g Release 2 introduced the PARALLEL_DEGREE_POLICY
initialization parameter to simplify parallel query. It has a default value of MANUAL
, but when set to AUTO
it enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
Oracle 12c Release 1 has added the ADAPTIVE
setting, which is similar to AUTO
, but includes performance feedback. In this case, the optimizer decides if a statement should run in parallel and what degree of parallelism (DOP) is appropriate. On completion, the actual performance of the statement is compared to the estimated performance from the initial optimization phase. If there is a significant difference between them, the actual performance statistics are stored as statistics feedback and the statement is marked as reoptimizable. The next time the statement is executed the statistics feedback is used to select a more appropriate DOP.
From 11gR2 onward, the use of a PARALLEL
hint in a statement will result in automatic DOP selection, regardless of the PARALLEL_DEGREE_POLICY
value.
Statistics Feedback and SQL Plan Directives (How They Interact)
This section is mostly speculation, based on my experience of using statistics feedback and speaking with others. I am not an optimizer developer, so I can not guarantee the following information is true. If I get any evidence to the contrary I will happily post it here.
I previously made the statement that statistics feedback does not get persisted as SQL plan directives. Although that seems to be true, the situation is a little more subtle.
Statistics feedback is an indication that the optimizer has made a bad choice. Typically these bad choices are because it is missing vital information when deciding on an execution plan. Statistics feedback can be used to feed back into the reoptimzation, but it doesn't actually solve the initial problem. The base statistics are still not representative!
SQL plan directives are "extra notes" that can prevent the optimizer making the same mistakes in future. In some circumstances, automatic reoptimization may result in the production of a SQL plan directive, but this does not contain the statistics/performance feedback itself, but rather instructions to perform dynamic sampling to resolve the discrepancy in the short term, so statistics feedback may no longer be necessary. Since SQL Plan directives influence the way future statistics are gathered by the DBMS_STATS
package, they also have the potential to fix the root cause of the problem by adding additional information (extended statistics) to the base statistics, making the SQL plan directive itself and the statistics feedback no longer necessary.
The example in this article uses a pipelined table function to demonstrate statistics feedback. Dynamic sampling of a pipelined table function is a really bad idea, as it will query the pipelined table function once to get the cardinality estimate, then again to process the actual statement. As a result, creating a SQL plan directive that suggests dynamic sampling of a pipelined table function would be a really bad idea and it seems the optimizer team have avoided this situation, which is good.
So what am I saying here? Well, a situation that requires the use of statistics feedback may also result in the creation of a SQL plan directive, but that doesn't mean the SQL plan directive contains a persisted version of the statistics feedback. If it were as simple as statistics feedback being persisted as sql plan directives, we would expect it to work for pipelined table functions too.
My article on SQL Plan Directives shows a situation where cardinality misestimates actually does result in the creation of SQL plan directives.
There seem to be some interesting interactions between statistics feedback and SQL plan directives when they are both created as a result of cardinality misestimates.
- In a situation where both statistics feedback and a SQL plan directive have been created in the SGA, but the SQL plan directive *has not* yet been persisted to the SYSAUX tablespace, the statistics feedback is used during reoptimization and the SQL plan directive is ignored.
- In a situation where both statistics feedback and a SQL plan directive have been created, and the SQL plan directive *has* been persisted to the SYSAUX tablespace, the SQL plan directive, and possibly the statistics feedback, is used during reoptimization.
Since SQL plan directives are only persisted periodically, this means the reoptimization you get could be totally different depending on how long you wait between the first and second execution of the SQL statement, making the outcome rather unpredictable.
Remember, this is just speculation!
For more information see:
- Automatic Reoptimization
- Reoptimization: Statistics Feedback
- Statistics Feedback - Frequently Asked Questions (Doc ID 1344937.1)
- PARALLEL_DEGREE_POLICY
- Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2)
- Adaptive Plans in Oracle Database 12c Release 1 (12.1)
- Dynamic Statistics in Oracle Database 12c Release 1 (12.1)
- SQL Plan Directives in Oracle Database 12c Release 1 (12.1)
- Optimizer with Oracle Database 12c
Hope this helps. Regards Tim...