Automatic re-optimization of SQL performance optimization (SQL TUNING) new features in oracle12c (Automatic reoptimization)

The adaptive query optimization in ORACLE12C is composed of a series of different characteristics. Like the Adaptive Planning (Adaptiveplans) feature, you can modify the execution plan at run time, but do not allow changes in the sequence of connections in the schedule. Automatic re-optimization is based on the learning of previous execution and feedback to the optimizer information, so the next time the statement is executed, a better plan will be generated.

1. Statistical information feedback (potential feedback)

The potential feedback (Cardinalityfeedback) is introduced in the ORACLE11R2. When the optimizer produces an execution plan, statistics missing, stale statistics, complex predicates, or complex operations may trigger the optimizer to monitor the potential of each operation in the plan. Once executed, if the difference between the evaluation and the actual potential is large, the actual potential will be present in the SGA for future use and the statement will be labeled as re-optimized. The next time the statement executes, it is re-optimized with a stored potential for better planning. The potential feedback is determined by the statement, and is lost when the instance restarts or the statements in the shared pool are stale. In oracle12c, the potential feedback has been renamed to statistical information feedback.

--Note:

1) Statistics information feedback in the SGA as Opt_estimate HINTS storage in the V$sql_reoptimization_hints view. The view and hints are not archived.

1.1. Example

The following code creates a pipeline table function, which describes the statistics information feedback

CONN Test1/[email protected]

--Create a type that supports table functions

DROP TYPE Tp_tf_tab;

DROP TYPE Tp_tf_row;

CREATE TYPE Tp_tf_row as OBJECT (

ID number,

Description VARCHAR2 (50)

);

/

CREATE TYPE Tp_tf_tab is TABLE of Tp_tf_row;

/

--Create a table function.

CREATE OR REPLACE FUNCTION f_tab_pl (p_rows in number) RETURN tp_tf_tabpipelined as

BEGIN

For I in 1.. P_rows LOOP

PIPE ROW (Tp_tf_row (i, ' Description for ' | | i));

END LOOP;

RETURN;

END;

/

We know that the optimizer always evaluates the potential of a pipeline table function based on the size of the database block, so we want to get an error evaluation of the pipeline function query potential. The following query returns 10 rows of data, but the optimizer evaluates 8168 rows, which is obviously wrong.

SELECT/*+ gather_plan_statistics */* from TABLE (F_TAB_PL (10));

SET linesize PAGESIZE 100

SELECT * from TABLE (dbms_xplan.display_cursor (format = ' allstatslast '));

Plan_table_output

-------------------------------------------------------------------------------------------------

sql_id 0ktmsgvczysxy, Child NUMBER0

-------------------------------------

SELECT/*+ gather_plan_statistics */* from TABLE (F_TAB_PL (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|      F_TAB_PL |  1 |     8168 | 10 |00:00:00.01 |

-------------------------------------------------------------------------------------------------

Sql>

Check the view column v$sql.is_reoptimizable the display optimizer has detected an incorrect potential assessment and marked the statement as being re-optimized.

COLUMN Sql_text FORMAT A50

COLUMN is_reoptimizable FORMAT A16

SELECT Sql_text, is_reoptimizable

From V$sql

WHERE sql_text like '%f_tab_pl% '

and sql_text not like '%v$sql% ';

Sql_text is_reoptimizable

-------------------------------------------------- ----------------

SELECT/*+ gather_plan_statistics */* from Y

TABLE (F_TAB_PL (10))

Sql>

If we run the statement again, we will see a more accurate evaluation of the potential, and one of the notes tells us that statistical feedback is used. At the same time, it is also noted that the child travel designator has changed.

SELECT/*+ gather_plan_statistics */* from TABLE (F_TAB_PL (10));

SET linesize PAGESIZE 100

SELECT * from TABLE (dbms_xplan.display_cursor (format = ' allstatslast '));

Plan_table_output

-------------------------------------------------------------------------------------------------

sql_id 0ktmsgvczysxy, Child number1

-------------------------------------

SELECT/*+ gather_plan_statistics */* FROM

TABLE (F_TAB_PL (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|      F_TAB_PL |    1 |     20 | 10 |00:00:00.01 |

-------------------------------------------------------------------------------------------------

Note

-----

-Statistics feedback used forthis statement

Sql>

--Note:

1) in the 11RR2 version, the attention section will be "cardinality feedbackused for this statement".

2) It is also mentioned in the documentation that the potential error evaluation also results in the generation of SQL plan directives (SQLPlan directives), but do not mistakenly assume that statistical feedback is kept in the SQL plan directives. We can query the existence of SQL plan directives by using the following statement.

CONN [email protected] 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>

2. Performance feedback (performance Feedback)

Oracle 11GR2 introduces Parallel_degree_policy initialization parameters to simplify parallel queries. The default value for this parameter is manual, which enables parallelism determination, statement queuing, and in-memory parallel execution automation when set to Auto.

Oracle 12CR1 adds a adaptive setting for this parameter, similar to auto, but includes performance feedback. In this case, the optimizer determines whether the statement runs in parallel and the appropriate degree of parallelism. When completed, comparisons are made between the actual performance of the statement and the evaluation performance of the initial optimization phase. If the difference between the two is very large, the actual performance statistics are stored as statistical information feedback, and the statement is also marked as re-optimized. When the statement is executed the next time, statistical feedback is used to select a more appropriate degree of parallelism (DOP).

--Note:

1) from the Oracle 11GR2 backwards, the parallel hint in the statement will cause the system to automatically select the degree of parallelism, regardless of the parallel_degree_policy set why value.

3. Statistical feedback and SQL plan directives (interaction)

This part is mostly based on my empirical speculation about statistical information feedback. The statements we tested earlier are not saved as SQL plan directives. Statistical feedback indicates that the optimizer has made bad choices, which are generally due to missing important information when determining the execution plan. Statistical information feedback can be used for re-optimization, but it does not solve the initial problem. Basic statistical information is still not representative.

SQL plan directives are "extra tips" that prevent the optimizer from making the same mistakes in the future. In some scenarios, automatic re-optimization can also lead to the generation of SQL plan execution, but this does not include statistical feedback and performance feedback, but rather the execution of dynamic sampling to solve short-term deviations, therefore, no need for statistical information feedback.

Because SQL plan directives affect the way the Dbms_stats package collects statistics in the future, by adding additional information to the underlying statistics (extended statistics), it has the ability to fundamentally resolve the problem, thus eliminating the need for SQL plan execution and statistical information feedback. A scenario that requires statistical feedback may generate SQL plan directives, but that does not mean that SQL plan directives contain a reserved version of statistical information feedback.

There are some interesting interactions between the statistical feedback and the SQL plan directives that are created when they are evaluated for potential errors:

Ø both are created in the SGA but SQL plan directives have not yet been persisted to the Sysaux table space, and statistical feedback is used during the re-optimization period, ignoring the existence of SQL plan directives.

Ø both are created but the SQL plan directives are persisted to the Sysaux table space, and the SQL plan instructions are used during the re-optimization, and statistical information feedback may also be used.

Because SQL plan directives are only periodically retained, this means that depending on the length of time between the first and second executions of the SQL statement, the final re-optimization may be completely different, resulting in unpredictable results.

Automatic re-optimization of SQL performance optimization (SQL TUNING) new features in oracle12c (Automatic reoptimization)