Oracle 12c Automatic Reoptimization

在自动重优化中，优化器会在初次执行后为后续的执行选择一个执行计划。对于所有类型的计划变化自适应查询计划是不可行的。例如，一个使用效率低的连接顺序的查询可以生成次优的执行计划，但自适应查询计划不支持在执行时调整连接顺序。在一个语句第一次执行之后，优化器使用在执行时所收集到的信息来判断使用自动重优化是否可以降低执行成本。如果收集到的执行信息与优化器所评估的存在显著差异，那么优化器会在下一次执行时查找一个可替代的执行计划。

优化器使用在之前执行时所收集到的信息来帮助决定一个替代执行计划。优化器可能会重新优化一个查询多次，每次收集额外的数据并且将来用于改进执行计划。自动重新优化包含以下两种形式:

统计信息反馈

性能反馈

统计信息反馈

一种重新优化形式叫作统计信息反馈(之前叫作基数反馈)，它能对存在错误基数评估的重复查询进行自动改进。优化器可能因为许多原因而造成基数错误评估，比如丢失统计信息，不正确的统计信息或者复杂的谓词条件。使用统计反馈执行重新优化的基本过程如下:

1.在第一次执行一个 SQL 语句时，优化器会生成一个执行计划。优化器可能在以下情况下对共享SQL区的统计反馈进行监控:

.没有统计信息的表

.一个表上的多个连接或分离过滤谓词

.包含复杂操作的谓词优化器不能精确的计算选择性

2.在第一次执行结束后，优化器将对初次所评估的基数与在执行时执行计划中每步操作所返回的真实行记录进行比较。

如果评估的基数与真实的基数存在显著差异，那么优化器将存储真实的基数给后续的执行所使用。优化器也会创建一个SQL计划指令因此其它的SQL语句可以从初次执行后所获得的信息中获益。

3.如果查询再次执行，那么优化器使用正确的基数评估来代替它的常用评估。optimizer_adaptive_statistics参数不能控制自动重优化的所有功能。这个参数只在自动重优化上下文中控制着对连接基数的统计信息反馈。例如，将

optimizer_adaptive_statistics参数设置为false时将禁用对连接基数错误评估进行统计信息反馈，但它不会禁用对单表基数错误评估进行统计信息反馈。

下面的例子显示了数据库如何使用统计信息反馈来调整不正的基数评估

1.用户oe来对表orders,order_items与product_information表进行查询:

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

2.查询游标中的执行计划显示评估的行记录(E-Rows)远远小于实际行记录(A-Rows)

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID  5sq2n361a0938, child number 2
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 1906736282

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |    269 |00:00:00.07 |    1339 |       |       |          |
|   1 |  NESTED LOOPS         |                     |      1 |      1 |    269 |00:00:00.07 |    1339 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|                     |      1 |      4 |   9135 |00:00:00.03 |      34 |       |       |          |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |      1 |      1 |     87 |00:00:00.01 |      33 |       |       |          |
|   4 |    BUFFER SORT        |                     |     87 |    105 |   9135 |00:00:00.01 |       1 |  4096 |  4096 | 4096  (0)|
|   5 |     INDEX FULL SCAN   | ORDER_PK            |      1 |    105 |    105 |00:00:00.01 |       1 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |   9135 |      1 |    269 |00:00:00.02 |    1305 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

3.用户oe重新执行步骤1中的查询

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

4.查询游标中的执行计划显示对于第二次执行优化器使用了统计信息反馈(Note部分)并且选择了一个不同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

Note
-----
   - statistics feedback used for this statement

在上面的输出中，评估的行记录是(269)与实际行记录相匹配。

5.用户oe再执行查询多次(两次以上)

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

6.查询游标中的执行计划显示对于第四次执行优化器使用了与第二次执行相同的执行计划(Note部分)并且选择了一个相同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

 

Note
-----
   - this is an adaptive plan

从上面的输出的Note部分this is an adaptive plan可知使用了自适应执行计划。

性能反馈

重新优化的另一种形式是性能反馈。这个重新优化当parallel_degree_policy参数设置为adaptive时用来帮助重复执行SQL语句自动选择并行度。使用性能反馈执行重新优化的过程如下:

1.当parallel_degree_policy参数设置为adaptive时，在第一次执行SQL语句时，优化器将决定是否以并行方式来执行SQL语句 ，如果使用并行，使用什么样的并行度来执行。优化器选择并行度是基于语句的所评估的性能。对于所有语句会启用额外的性能监控。

2.在第一次执行结束后，优化器将比较以下信息:

.优化器所选择的并行度。

.基于在实际执行语句时所收集的性能统计数据所计算出来的并行度。

如果两个并行度存在显著差异，那么数据库将会标记语句重新解析，并且存储每一次执行的统计数据作为反馈。这种反馈将用来对后续的执行更好的计算并行度。

3.如果查询再次执行，优化器将使用第一次执行所收集到的性能统计数据来更好的决定执行语句的并行度。

注意即使parallel_degree_policy没有被设置为adaptive，统计信息反馈也可能影响对语句的并行度的选择。