Oracle Optimizer Adaptive Execution plan (Adaptive execution plans)

• • Overview
  • Adaptive Connection Method Ajm
    • AJM Example
    • Process flow of AJM
  • Adaptive parallel allocation Method APDM
    • APDM Example
    • Process flow of APDM
    • About allocation methods
  • Summarize
  • Reference

Overview

We know that before the 12c version, although the ACS, CFB and other functions by collecting information during the SQL text execution, to improve the execution plan when the SQL text is executed again, but when the first execution of SQL, only based on the statistical information of the execution plan execution of SQL, can not be changed during execution.
If the statistics are inaccurate, the number of data rows accessed is very large and the execution plan chosen is not optimal, and the first execution of SQL may cause catastrophic performance problems.

The Adaptive Execution plan (Adaptive execution plans AP) is a very important feature in the 12C Adaptive Query Optimization feature set that enables SQL to dynamically change the final execution plan based on runtime statistics at the first run. Used to avoid catastrophic performance problems when SQL statements are first executed due to poor execution plans.

It mainly includes the following 2 aspects of the components:

自适应连接方法（Adaptive Join Methods 以后简称AJM）自适应并行分布方法（Adaptive Parallel Distribution Methods 以后简称APDM）

The adaptive connection method AJM is mainly used to change the connection mode of the table;
The adaptive Parallel Distribution method (APDM) is primarily used to change the way data is distributed across parallel server processes in parallel execution.

Let's look at the ACS functionality in a few examples below.

Adaptive connection Method (AJM)

The adaptive Connection method (AJM) is able to switch the execution plan connection method when the SQL text is executed for the first time, thus obtaining the optimal execution plan.

AJM Example

Let's use the following example to understand how the adaptive connection method changes the table's connection.

1. View the information in the test table

SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS‘;   SQL> select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables where TABLE_NAME in (‘PRODUCT_INFORMATION‘,‘ORDER_ITEMS‘);TABLE_NAME             NUM_ROWS     BLOCKS-------------------- ---------- ----------PRODUCT_INFORMATION         288         13ORDER_ITEMS                 665          5SQL> select count(*) from ORDER_ITEMS;  COUNT(*)----------       665SQL> select count(*) from PRODUCT_INFORMATION;  COUNT(*)----------       288

2. View the execution plan with the explain plan for command

Sql> alter session set Statistics_level=all; Session altered. Sql> set linesize 200sql> set PAGESIZE 1000sql> EXPLAIN PLAN for 2 SELECT product_name 3 from Order_  Items O, product_information p 4 WHERE o.unit_price = 5 and Quantity > 1 6 and p.product_id = o.product_id 7; explained.sql>--default plansql> SELECT * from TABLE (Dbms_xplan. DISPLAY); Plan_table_  OUTPUT--------------------------------------------------------------------------------------------------------Plan Hash value:1255158658------------------------------------------------------------------------------------------ -------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |-------------------------------------------------------------------------------------------------------| 0 |                        SELECT STATEMENT |     |   4 |     128 | 7 (0) |   00:00:01 | |  1 |           NESTED LOOPS     |     |   4 |     128 | 7 (0) |   00:00:01 | |   2 |                        NESTED LOOPS |     |   4 |     128 | 7 (0) | 00:00:01 | |    * 3 | TABLE ACCESS Full |     Order_items |    4 |     48 | 3 (0) | 00:00:01 | |    * 4 | INDEX UNIQUE SCAN |     PRODUCT_INFORMATION_PK |       1 |     | 0 (0) |   00:00:01 | |   5 | TABLE ACCESS by INDEX rowid|     product_information |    1 |     20 | 1 (0) | 00:00:01 |------------------------------------------------------------------------------------------------------ -predicate information (identified by Operation ID):---------------------------------------------------3-filter ("O" ." Unit_price "=15 and" QUANTITY ">1" 4-access ("P". ") product_id "=" O "." product_id ")

Based on this output, we know that the optimizer chooses the execution plan based on the statistics, and Order_items and product_information are combined using the nested LOOPS (default plan).

3. Execute the SQL text and view the actual execution plan.

Sql> SELECT product_name 2 from Order_items o, product_information P 3 WHERE o.unit_price = 4 and Quanti Ty > 1 5 and p.product_id = o.product_id 6; Product_Name--------------------------------------------------Screws <b.28.s&gt ... Screws <b.28.s>13 rows selected. Sql>--View execution Plan sql> select * FROM table (dbms_xplan.display_cursor (format=> ' typical iostats ')); Plan_table_ OUTPUT--------------------------------------------------------------------------------------------------------SQL _id 7hj8dwwy6gm7p, child number 0-------------------------------------SELECT product_name from Order_items o, product_i nformation p whereo.unit_price = quantity > 1 and p.product_id = O.product_idplan Hash value:1553478007-- --------------------------------------------------------------------------------------------------------------- -------| Id | Operation | Name | Starts | E-rows | E-time |   A-rows | A-tiMe | Buffers | Reads |---------------------------------------------------------------------------------------------------------   ---------------| 0 |                     SELECT STATEMENT |      |        1 |          |     |      13 |00:00:00.04 |     24 | 20 | |  * 1 |                     HASH JOIN |      |      1 | 4 |     00:00:01 |      13 |00:00:00.04 |     24 | 20 | |   * 2 | TABLE ACCESS full|      Order_items |      1 | 4 |     00:00:01 |       13 |00:00:00.02 |      7 |   6 | |   3 | TABLE ACCESS full|      product_information |      1 | 1 |    00:00:01 |      288 |00:00:00.01 |     17 | ------------------------------------------------------------------------------------------------------------| ------------predicate information (identified by Operation ID):---------------------------------------------------1 -Access ("P". " product_id "=" O "." product_id ") 2-filter (" O "." Unit_price "=15 and" QUANTITY ">1)" Note------This was an adaptive plan26 rows selEcted. 

Based on this output, we find that there is a gap between E-rows and A-rows, and that the optimizer eventually Order_items and product_information will be combined using the hash JOIN method (default plan).

4. Let's pass the Dbms_xplan. Display (format=> ' +adaptive ') to view the complete execution plan.

sql> SET linesize 200sql> SELECT * from TABLE (dbms_xplan.display_cursor (' 7hj8dwwy6gm7p ', NULL, ' +adaptive ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------SQL _id 7hj8dwwy6gm7p, child number 0-------------------------------------SELECT product_name from Order_items o, product_i nformation p whereo.unit_price = quantity > 1 and p.product_id = O.product_idplan Hash value:1553478007--   --------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |----------------------------------------------------------------------------------------------------------     | 0 |                        SELECT STATEMENT |       |       |     |          7 (100) |  ||  * 1 |                        HASH JOIN |     |   4 |     128 | 7 (0) | 00:00:01 | |   -2 |                        NESTED LOOPS |     |   4 |     128 | 7 (0) | 00:00:01 | |    -3 |                        NESTED LOOPS |     |   4 |     128 | 7 (0) | 00:00:01 | |     -4 |                        STATISTICS collector★|       |       |            |          |  ||      * 5 | TABLE ACCESS Full |     Order_items |    4 |     48 | 3 (0) | 00:00:01 | |     -* 6 | INDEX UNIQUE SCAN |     PRODUCT_INFORMATION_PK |       1 |     |          0 (0) | ||    -7 | TABLE ACCESS by INDEX rowid|     product_information |    1 |     20 | 1 (0) |     00:00:01 | |   8 | TABLE ACCESS Full |     product_information |    1 |     20 | 1 (0) | 00:00:01 |------------------------------------------------------------------------------------------------------ ----predicate information (identified by Operation ID):---------------------------------------------------1-access ( "P". " product_id "=" O "."product_id") 5-filter ("O". " Unit_price "=15 and" QUANTITY ">1)" 6-access ("P". ") product_id "=" O "." product_id ") Note------This was an adaptive plan (rows marked '-' be inactive) and the rows selected.

We can refer to the illustrations in Oracle White Paper June Optimizer with Oracle Database 12c to describe the above procedure.

Process flow of AJM

According to the above example, we can see that the process of AJM mainly includes the following processes:

The AJM process can be described as follows:

1.根据现有统计信息生成默认的执行计划和可切换的辅助执行计划（subPlan）2.执行默认的执行计划，并同时通过统计收集器收集实际的统计信息（如行数等）3.根据收集的实际统计信息和辅助执行计划切换临界值，从多个辅助执行计划（subPlan）中选择最优的一个，作为最终的执行计划。

Adaptive parallel Allocation Method (APDM)

The adaptive Parallel Allocation Method (APDM) is primarily used to change the way data is distributed across parallel server processes when parallel execution occurs.

When SQL executes operations such as parallel sorting, aggregation operations, and joins in parallel, it needs to be processed after the data is redistributed between the various parallel server processes executing SQL, and the data allocation method is determined by the number of parallel server processes and the number of processing rows estimated by the optimizer during the operation.

If the optimizer estimates the number of processing rows based on statistical information is inaccurate, it will result in the selection of the allocation method is not optimal, so that can not take full advantage of all parallel server processes, affecting performance.

Therefore, on 12c, Oracle introduces a new adaptive allocation method:

可以能够使SQL根据运行时统计信息，进行广播式分配方法（broadcast distribution）和哈希分配方法（ hash distribution）的切换，在运行时决定最终的数据分配方法。

APDM Example

Sql> Conn hr/hrconnected.sql> set linesize 200sql> set PAGESIZE 1000sql> EXPLAIN PLAN for SELECT/*+ Paralle L (4) Full (e) Full (d) */Department_name, sum (salary) from Employees e, departments D WHERE D.department_id=e.departme  nt_id GROUP by Department_name; 2 3 4 5 6 explained.sql> SELECT * from TABLE (Dbms_xplan. DISPLAY); Plan_table_output-----------------------------------------------------------------------------------------Plan Hash value:2940813933----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |    Time | TQ | In-out|   PQ Distrib |----------------------------------------------------------------------------------------| 0 |             SELECT STATEMENT |    |   27 |     621 | 5 (20) |        00:00:01 |      |            |   ||  1 |             PX Coordinator |       |       |  |          |        |      |            |   ||   2 | PX SEND QC (RANDOM) |    : TQ10003 |   27 |     621 | 5 (20) |  00:00:01 | q1,03 | P->s |   QC (RAND) | |    3 |             HASH GROUP by |    |   27 |     621 | 5 (20) |  00:00:01 | q1,03 |            PCWP |   ||     4 |             PX RECEIVE |    |   27 |     621 | 5 (20) |  00:00:01 | q1,03 |            PCWP |   ||      5 | PX SEND HASH |    : TQ10002 |   27 |     621 | 5 (20) |  00:00:01 | q1,02 | P->p |   HASH | |       6 |             HASH GROUP by |    |   27 |     621 | 5 (20) |  00:00:01 | q1,02 |            PCWP | ||        * 7 |             HASH JOIN |   |  106 |     2438 | 4 (0) |  00:00:01 | q1,02 |            PCWP |   ||         8 |             PX RECEIVE |    |   27 |     432 | 2 (0) |  00:00:01 | q1,02 |            PCWP |   ||          9 | PX SEND HYBRID HASH | : TQ10000   |   27 |     432 | 2 (0) |  00:00:01 | q1,00 | P->p |  HYBRID hash| |           10 |             STATISTICS COLLECTOR |       |       |            |          |  | q1,00 |            PCWC |  ||            11 |             PX BLOCK ITERATOR |    |   27 |     432 | 2 (0) |  00:00:01 | q1,00 |            PCWC |  ||             12 | TABLE ACCESS Full |    Departments |   27 |     432 | 2 (0) |  00:00:01 | q1,00 |            PCWP |  ||         13 |             PX RECEIVE |   |   107 |     749 | 2 (0) |  00:00:01 | q1,02 |            PCWP |  ||          14 | PX SEND HYBRID HASH (SKEW) |   : TQ10001 |   107 |     749 | 2 (0) |  00:00:01 | q1,01 | P->p |  HYBRID hash| |           15 |             PX BLOCK ITERATOR |   |   107 |     749 | 2 (0) |  00:00:01 | q1,01 |            PCWC |  ||            16 | TABLE ACCESS Full |   EMPLOYEES |   107 |     749 | 2 (0) |  00:00:01 | q1,01 |            PCWP | |------------------------------------------------------------------------------------------predicate information (identified by Operation ID):------ ---------------------------------------------7-access ("D". " department_id "=" E "." department_id ") Note------degree of Parallelism is 4 because of hint32 rows selected.

Please note that the hybrid hash is used in the execution plan and the statistics COLLECTOR is implanted.

Process flow of APDM

APDM mainly includes the following processes:

APDM Processing Process:

1.根据现有统计信息生成使用混合型哈希分配方法（HYBRID HASH）的执行计划，并在执行分配方法操作前，预置统计收集器。2.执行默认的执行计划，并同时通过统计收集器收集实际的统计信息（如行数等）3.根据收集的实际统计信息，如果实际访问行数比临界值小，就把数据分配方法切换成广播式分配方法（broadcast distribution）  否则，如果实际访问行数达到临界值，就切换为哈希分配方法（ hash distribution）。  其中临界值（threshold)为并行度的2倍。

About allocation methods

The following is a simple introduction to the data allocation method.

Broadcast allocation method (broadcast distribution):

会把查询结果集发送到所有的并行服务器进程。（每个并行服务器进程都会含有一份相同的查询结果集）。一般适合并行服务器进程很多，而结果集行数很少的情况。

Hash allocation method (hash distribution):

会把查询结果集通过哈希算法分成N份，发送到各个并行服务器进程。（每个并行服务器进程包含一份查询结果集的子集）.一般适合并行服务器进程较少，而结果集行数较多的情况。

Summarize

In this chapter, we introduce the adaptive connection method (Adaptive Join Methods) and the Adaptive Parallel distribution method (Adaptive Parallel distribution Methods) introduced by Oracle 12c through the above examples and summaries.

Copyright NOTICE: This article for Bo Master original article, reprint must indicate the source, I reserve all relevant powers! Http://blog.csdn.net/lukeunique

Reference:

https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL221

Adaptive plans

https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL94854

Reading Adaptive Plans

Oracle White Paper June Optimizer with Oracle Database 12c

Oracle Optimizer Adaptive Execution plan (Adaptive execution plans)