Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics.
This new approach is extremely helpful when existing statistics are not sufficient to generate an optimal plan. There are two distinct aspects in Adaptive Query Optimization: adaptive plans, which focuses on improving the execution of a query and adaptive statistics, which uses additional information to improve query execution plans.
Adaptive Plans
An adaptive plan will be chosen by the Optimizer if certain conditions are met; for example, when a query includes joins and complex predicates that make it difficult to estimate cardinality accurately. Adaptive plans enable the optimizer to defer the plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan), with statistics collectors so that at runtime, it can detect if cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance.
From Oracle Database 12.2 onwards, adaptive plans are enabled and disabled using the database parameter optimizer_adaptive_plans. Use of the default value of TRUE (enabled) is recommended.
ADAPTIVE JOIN METHODS
The optimizer is able to adapt join methods on the fly by predetermining multiple sub-plans for portions of the plan. For example, in Figure 3, the optimizer’s default plan choice for joining the orders and products tables is a nested loops join via an index access on the products table. An alternative sub-plan, has also been determined that allows the optimizer to switch the join type to a hash join. In the alternative plan the products table will be accessed via a full table scan.
During the initial execution, the statistics collector gathers information about the execution and buffers a portion of rows coming into the sub-plan. The Optimizer determines what statistics are to be collected, and how the plan should be resolved for different values of the statistics. It computes an “inflection point” which is the value of the statistic where the two plan choices are equally good. For instance, if the nested loops join is optimal when the scan of an orders table produces fewer than 10 rows, and the hash join is optimal when the scan of orders produces more than 10 rows, then the inflection point for these two plans is 10. The optimizer computes this value, and configures a buffering statistics collector to buffer and count up to 10 rows. If at least 10 rows are produced by the scan, then the join method is resolved to hash join; otherwise it is resolved to nested loops join. In Figure 3, the statistics collector is monitoring and buffering rows coming from the full table scan of orders. Based on the information seen in the statistics collector, the optimizer will make the decision about which sub-plan to use. In this case, the hash join is chosen since the number of rows coming from the orders table is larger than the optimizer initially estimated.
The optimizer can switch from a nested loops join to a hash join and vice versa. However, if the initial join method chosen is a sort merge join no adaptation will take place.
By default, the explain plan command will show only the initial or default plan chosen by the optimizer. Whereas the DBMS_XPLAN.DISPLAY_CURSOR function displays the plan actually used by the query.
To see all of the operations in an adaptive plan, including the positions of the statistics collectors, the additional format parameter ‘adaptive’ must be specified in the DBMS_XPLAN functions. In this mode, an additional notation “-” appears in the Id column of the plan, indicating the operations in the plan that were not used (inactive).
SQL Monitor visualizes all operations if “Full” is selected in the “Plan” drop down box. The inactive parts of the plan are grayed out (see Figure 6). If the “Plan Note” icon is clicked, a pop-up box will be displayed confirming that the plan is an adaptive plan.
ADAPTIVE PARALLEL DISTRIBUTION METHODS
When a SQL statement is executed in parallel certain operations, such as sorts, aggregations, and joins require data to be redistributed among the parallel server processes executing the statement. The distribution method chosen by the optimizer depends on the operation, the number of parallel server processes involved, and the number of rows expected. If the optimizer inaccurately estimates the number of rows, then the distribution method chosen could be suboptimal and could result in some parallel server processes being underutilized.
With the new adaptive distribution method, HYBRID HASH the optimizer can defer its distribution method decision until execution, when it will have more information on the number of rows involved. A statistics collector is inserted before the operation and if the actual number of rows buffered is less than the threshold the distribution method will switch from HASH to BROADCAST. If however the number of rows buffered reaches the threshold then the distribution method will be HASH. The threshold is defined as 2 X degree of parallelism.
Figure 7 shows an example of a SQL Monitor execution plan for a join between EMP and DEPT that is executed in parallel. One set of parallel server processes (producers or pink icons) scan the two tables and send the rows to another set of parallel server processes (consumers or blue icons) that actually do the join. The optimizer has decided to use the HYBRID HASH distribution method. The first table accessed in this join is the DEPT table. The rows coming out of the DEPT table are buffered in the statistics collector, on line 6 of the plan, until the threshold is exceeded or the final row is fetched. At that point the optimizer will make its decision on a distribution method.
To understand which distribution method was chosen at runtime, the easiest way to find this information is to look at the OTHER column in SQL Monitor. This column shows a binocular icon in the lines with PX SEND HYBRID HASH row source. When you click the icon, you can see the distribution method used at runtime.
For the adaptive distribution methods there are three possible values reported in this dialog box: 6 = BROADCAST, 5 = ROUND-ROBIN, and 16 = HASH distribution.
ADAPTIVE BITMAP INDEX PRUNING
When the optimizer generates a star transformation plan, it must choose the right combination of bitmap indexes to reduce the relevant set of ROWIDs as efficiently as possible. If there are many indexes, some of them might not reduce the ROWID set very substantially but will nevertheless introduce significant processing cost during query execution. Adaptive plans are therefore used to prune out indexes that are not significantly filtering down the number of matched rows.
DBMS_XPLAN.DISPLAY_CURSOR will reveal adaptive bitmap pruning in a SQL execution plan with the adaptive keyword in a similar manner to the example shown in Figure 4. For example, consider the following SQL execution plan showing the bitmap index CAR_MODEL_IDX being pruned:
No comments:
Post a Comment