8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related articles.

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 a 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.

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:

Hope this helps. Regards Tim...

Back to the Top.