oracle12c 自适应执行计划(Adaptive Execution Plans)

郑全

概述
我们知道在12c之前的版本，虽然有ACS、CFB等功能通过在SQL执行时收集信息，来改善SQL再次执行时的执行计划，但是在SQL第一次执行时，只能根据统计信息做成的执行计划执行SQL，在执行过程中并不能改变。
如果统计信息不准确，访问的数据行数非常大并且选择的执行计划不是最优时，在SQL第一次执行时可能会引起在灾难性的性能问题。
自适应执行计划(Adaptive Execution Plans 以后简称AP)是 12C 自适应查询优化功能集合中非常重要的一项功能，它能够使SQL在第一次运行时根据运行时统计信息动态改变最终的执行计划，用于避免SQL语句在第一次执行时由于差的执行计划引起灾难性的性能问题。


其主要包括以下2方面的组件：
自适应连接方法（Adaptive Join Methods 以后简称AJM）
自适应并行分布方法（Adaptive Parallel Distribution Methods 以后简称APDM）
12


自适应连接方法AJM主要用于改变表的连接方式；
自适应并行分布方法（APDM）主要用于在并行执行时，改变数据在各并行服务器进程的分布方式。
下面我们将通过几个例子来了解一下ACS功能。

自适应连接方法（AJM）
自适应连接方法（AJM）能够在SQL第一次执行时也能进行执行计划连接方法的切换，从而得到最优的执行计划。

AJM例
让我们通过下面的例子，对自适应连接方法改变表的连接方式的内容进行理解。
1.查看测试表的信息
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         13
ORDER_ITEMS                 665          5
SQL> select count(*) from ORDER_ITEMS;
  COUNT(*)
----------
       665
SQL> select count(*) from PRODUCT_INFORMATION;
  COUNT(*)
----------
       288
1234567891011121314151617181920
2.通过EXPLAIN PLAN FOR命令来查看执行计划
SQL> alter session set statistics_level=all;
Session altered.
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 1000
SQL> EXPLAIN PLAN FOR
  2    SELECT product_name
  3    FROM      order_items o, product_information p
  4    WHERE  o.unit_price = 15
  5    AND       quantity > 1
  6    AND       p.product_id = o.product_id
  7  ;
Explained.
SQL> --default plan
SQL> 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")
1234567891011121314151617181920212223242526272829303132333435363738
根据这个输出，我们知道优化器根据统计信息选择的执行计划为，ORDER_ITEMS和PRODUCT_INFORMATION会利用NESTED LOOPS 的方式进行结合（default plan）。
3.执行SQL并查看实际的执行计划。
SQL> SELECT product_name
  2  FROM   order_items o, product_information p
  3  WHERE  o.unit_price = 15
  4  AND    quantity > 1
  5  AND    p.product_id = o.product_id
  6  ;
PRODUCT_NAME
--------------------------------------------------
Screws <B.28.S>
...
Screws <B.28.S>
13 rows selected.
SQL>
--查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost -bytes'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  7hj8dwwy6gm7p, child number 0
-------------------------------------
SELECT product_name FROM   order_items o, product_information p WHERE
o.unit_price = 15 AND    quantity > 1 AND    p.product_id = o.product_id
Plan 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 |     14 |
------------------------------------------------------------------------------------------------------------------------
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 is an adaptive plan

26 rows selected.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
根据这个输出，我们发现E-Rows和A-Rows有一定的差距，并且知道优化器最终ORDER_ITEMS和PRODUCT_INFORMATION会利用HASH JOIN  的方式进行结合（default plan）。
4.让我们通过DBMS_XPLAN.DISPLAY(FORMAT=>’+ADAPTIVE’)的方式，查看完整的执行计划。
SQL> SET LINESIZE 200
SQL> 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_information p WHERE
o.unit_price = 15 AND    quantity > 1 AND    p.product_id = o.product_id
Plan 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 is an adaptive plan (rows marked '-' are inactive)

32 rows selected.
123456789101112131415161718192021222324252627282930313233343536373839
我们可以引用Oracle White Paper June 2013 Optimizer with Oracle Database 12c中的插图料描述以上的过程。

登录/注册后可看大图