Shulou(Shulou.com)06/01 Report--

The Oracle 12c database does make a lot of progress in terms of optimizer. Among the many features of Oracle 12c database, adaptive query optimization is a big change. It enables the optimizer to adjust the execution plan in real time. When existing statistics are insufficient to produce an optimized plan, it can find additional information that leads to better statistics in a timely manner. Adaptive query optimization includes two aspects:

Adaptive plan, which focuses on improving the initial execution of a query

Adaptive statistics, which provide additional information for subsequent execution.

(components of adaptive query optimization)

The following is a summary and introduction of adaptive statistics.

Adaptive statistics

In oracle, the quality of the execution plan chosen by the optimizer depends largely on the quality of the statistics available. However, some query predicates become too complex to rely on base table statistics alone, which is why we often encounter complex SQL that still choose a poor execution plan when there is no problem with the statistics, and now the optimizer can supplement it with adaptive statistics in oracle 12c.

Adaptive statistics mainly include three aspects: dynamic sampling (dynamic statistics), automatic re-optimization and SQL planning instructions.

1. Dynamic statistical information

When a SQL statement is compiled with missing, out-of-date, or insufficient statistics, the execution plan chosen by the CBO optimizer may not be the best or even the worst, and dynamic sampling is precisely to compensate for insufficient statistics, which can lead to a very bad plan if not.

When the statistics of a table are missing, the optimizer first uses dynamic sampling on those tables to collect basic statistics. The statistics collected in this case are not as good as those collected using the DBMS_STATS package in terms of quality and integrity. This is the case when the default level of dynamic sampling in the database is 2.

Level of dynamic sampling

In Oracle 12c database, dynamic sampling is enhanced and transformed into dynamic statistical information, which enables the optimizer to get a more accurate cardinality estimation on the existing statistical information. This is not only for access to a single table, but also for join and grouping calculations.

The initialization parameter OPTIMIZER_DYNAMIC_SAMPLING introduces a new sampling level of 11.

Level 11 allows oracle CBO to choose whether or not to use dynamic statistics for SQL statements, even if statistics already exist in the relevant table. CBO makes a decision on whether to use dynamic statistics based on existing statistics, the complexity of predicates used by SQL, and the expected execution time.

In the past, CBO sometimes only made guesses when generating execution plans in some cases, but now it is possible to get more accurate calculations through dynamic statistics.

Dynamic statistics make the evaluation of the execution plan more accurate, and in the absence of dynamic sampling, the optimizer can only guess:

The biggest advantage of dynamic statistics is that when the optimizer chooses the execution plan, it can dynamically collect statistics for the objects whose statistics are missing or not accurate enough, so as to obtain a relatively good execution plan. Moreover, the optimizer in 12c can also keep the query results as dynamic statistics in the cache for other SQL to share these statistics.

However, we know that any new feature is a double-edged sword with both advantages and disadvantages, as well as for dynamic statistics. When the level is set to 11:00, dynamic sampling is likely to be enabled more frequently than before. And the parsing time during hard parsing may become longer, and if a large number of dynamic collection operations occur, it may affect the overall performance of the database.

2. Automatic re-optimization

Different from the adaptive plan introduced earlier, the adaptive plan gives the correct execution plan before the initial implementation, while the automatic re-optimization modifies the execution plan in the later execution after the initial implementation.

At the end of the initial execution of a SQL statement, CBO uses the information collected during the initial execution to determine whether automatic reoptimization is required. If the result of the execution is too different from the original estimate of the optimizer, CBO will execute the plan looking for a replacement next time. CBO uses the information collected from the previous execution to help determine the replacement plan. CBO may re-optimize a query several times, each time learning and further improving the plan.

Oracle 12c databases only need to be re-optimized in the following ways.

2.1. Statistical information feedback

Statistical feedback, which automatically improves plans for queries that are executed repeatedly with cardinality estimation errors. When CBO generates an execution plan during the first execution of a SQL statement, it decides whether a statistics feedback monitor should be started for cursors.

Statistics feedback is enabled when a table with missing statistics has multiple conjunctive or disjunctive predicates (predicates for AND or OR joins) that contain complex operations that make it impossible for CBO to accurately estimate cardinality.

At the end of the query, CBO compares its original cardinality estimate with the actual cardinality observed during execution, and if there is a significant difference between the estimated value and the actual value, it will store the correct value for later use. If the query is executed again, the optimizer uses the corrected cardinality estimate instead of its original estimate to determine the execution plan. If it finds that the initial estimate is correct, no additional measures will be taken.

The following is an example to demonstrate statistical feedback:

It can be seen that the factor between the calculated cardinality (E-Rows) and the actual cardinality (A-Rows) in the first generated execution plan is 8 (63: 500), which is incorrect, which indicates that the execution plan here is not the most accurate.

After the initial execution, the optimizer compares its original cardinality estimate with the actual number of rows returned by the planned operation. There is a big difference between the estimated value and the actual number of rows returned, so the cursor is marked IS_REOPTIMIZIBLE (re-optimizable) and will not be used again. The IS_REOPTIMIZIBLE attribute indicates that the SQL statement should be hard parsed the next time it is executed, so the optimizer can use the statistics recorded during the initial execution to determine a better execution plan.

In the second execution, the optimizer uses statistics from the initial execution to determine a new plan with a different join order. The use of statistical feedback during the generation of the execution plan is indicated in the comments section below the execution plan.

After the second use of cardinality feedback, the new plan is not identified as IS_REOPTIMIZIBLE, so it will be used by all subsequent executions of this SQL statement. After that, the optimizer turns off the monitoring of statistical feedback.

2.2, performance feedback

Another form of oracle 12c automatic re-optimization is performance feedback, which mainly turns on the automatic parallelism selection function in adaptive mode, thus improving the efficiency of repeated SQL.

When automatic parallelism is enabled, during the first execution of a SQL statement, the optimizer determines whether the statement should be executed in parallel mode, and if so, what parallelism should be used. At the end of the initial execution, the degree of parallelism selected by the optimizer is compared with the degree of parallelism calculated based on the actual performance statistics (such as CPU time) during the initial execution of the statement. If there is a significant difference between the two values, the statement is identified as re-optimizable, and the performance statistics of the initial execution are stored as feedback to help calculate a more appropriate degree of parallelism for subsequent execution.

Oracle can automate the selection of parallelism when it is set to AUTO when 11gR2 introduces the PARALLEL_DEGREE_POLICY initialization parameter. The default value of this parameter is MANUAL.

Oracle 12cR1 adds an ADAPTIVE setting to this parameter, which is similar to AUTO but includes performance feedback.

After PARALLEL_DEGREE_POLICY is adjusted to ADAPTIVE, a statement that does not require that much parallelism is deliberately opened to 128parallelism.

In the second execution, the performance feedback mechanism has failed, and CBO automatically chooses a parallelism of 1. 5 for the statement.

3. SQL plan instruction

SQL plan instructions are created based on information learned through automatic re-optimization. Starting from the version of oracle 12C, oracle launched the function of sql Plan instruction (SQL Plan directives referred to as SPD), which saves some instructions and additional information to the dictionary table in order to generate the optimal execution plan in the future, so as to achieve the purpose of persistence. The SQL plan instruction aims at some additional information about SQL, and the optimizer can be used to generate a better execution plan.

With regard to SQL plan instructions, I have given a detailed introduction to SQL Plan Directives (SQL execution Plan instruction), a new weapon of the ORACLE 12C optimizer in my article.

Http://note.youdao.com/noteshare?id=c30735781a4f69041bcd3c62537cee98

4. Adaptive query optimization related initialization parameters

There are several new initialization parameters that manage the optimizer of the Oracle 12c database and its new features. The following is a detailed description of these new parameters.

OPTIMIZER_ADAPTIVE_FEATURES

The use of new adaptive query optimization features, including adaptive joins, the creation and use of SQL plan instructions, is controlled by the OPTIMIZER_ADAPTIVE_FEATURES parameter. The default value of this parameter is related to OPTIMIZER_FEATURES_ENABLE (OFE). If this parameter is set to 12.1.0.1 or higher, then OPTIMIZER_ADAPTIVE_FEATURES is set to TRUE and all adaptive query optimization features will be enabled. If OFE is set lower than 12.1.0.1, then OPTIMIZER_ADAPTIVE_FEATURES will be set to FALSE and all adaptive query optimization features will not be enabled.

OPTIMIZER_ADAPTIVE_REPORTING_ONLY

After adaptive query optimization is turned on, adaptive or midway changes in the execution plan can make people worry about whether it is dangerous or not. To better understand how many SQL statements will be affected by the new adaptive plan, you can turn the adaptive plan on to report-only mode by setting OPTIMIZER_ADAPTIVE_REPORTING_ONLY to TRUE (the default is FALSE). In this mode, the information required to enable the adaptive connection method is collected, but there is no action to modify the plan. This means that the default plan is always used, but information on how the plan will be adjusted in non-reporting mode is also collected.

OPTIMIZER_DYNAMIC_SAMPLING

The OPTIMIZER_DYNAMIC_SAMPLING parameter has been available since 9i, and at 12c it has a new level 11, which controls the generation of dynamic statistics. When set to level 11:00, the optimizer automatically determines which statements benefit from dynamic statistics, even if all objects already have statistics.

5. Various pits of adaptive query optimization

Again, any new function is a double-edged sword, which brings us convenience and will dig a lot of holes to fill it slowly.

Conclusion

The optimizer is one of the most attractive parts of the Oracle database because of its complexity. Its goal is to determine the most efficient execution plan for each SQL statement. In the new features of adaptive query optimization of optimizer in oracle 12c, we can see that oracle has made great efforts in the quality pursuit of optimizer. Smarter and smarter optimizers can reduce more SQL performance problems, but because the optimizer is a very complex component, it always encounters a variety of hidden dangers in the early stages. Therefore, we need more in-depth understanding and prevention before upgrading and upgrading.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.