适用于:
Oracle Database - Enterprise Edition - 版本 12.1.0.1 和更高版本Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台
用途
介绍 12cR1 版本引入的自适应执行计划特性。
适用范围
面向性能调优方向的工程师和客户。
详细信息
对于之前的 Oracle 版本，一旦执行计划确定后，SQL 就会按照它严格执行，但有时候会引起问题，例如下面的执行计划：
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 127 | 385 (19)| 00:00:05 |
| 1 | NESTED LOOPS SEMI | | 1 | 127 | 385 (19)| 00:00:05 |
| 2 | NESTED LOOPS SEMI | | 1 | 106 | 382 (19)| 00:00:05 |
| 3 | NESTED LOOPS | | 1 | 85 | 379 (19)| 00:00:05 |
| 4 | NESTED LOOPS | | 18 | 972 | 322 (22)| 00:00:04 |
| 5 | SORT UNIQUE | | 18 | 378 | 312 (23)| 00:00:04 |
|* 6 | INDEX FAST FULL SCAN | PK_DICT_BATCH_NUMBERS | 18 | 378 | 312 (23)| 00:00:04 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| DICT_VET_PROD | 1 | 33 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_DICT_VET_PROD | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | DICT_BATCH_NUMBERS | 1 | 31 | 4 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_DICT_LOT_LINK | 1 | | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PK_DICT_BATCH_NUMBERS | 1 | 21 | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | PK_DICT_BATCH_NUMBERS | 1 | 21 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(DECODE(NULL,NULL,'ccc',"BATCH_TYPE")='ccc' AND DECODE(NULL,NULL,'ccc',UPPER("BATCH_NO"))='ccc')
8 - access("PROD_CODE"="A"."PROD_CODE")
10 - access("A"."PROD_CODE"="B"."PROD_CODE" AND "LOT_LINK_NO"="LOT_LINK_NO")
filter("LOT_LINK_NO"="LOT_LINK_NO")
11 - access("PROD_CODE"="A"."PROD_CODE" AND "LOT_LINK_NO"="LOT_LINK_NO")
filter("LOT_LINK_NO"="LOT_LINK_NO" AND DECODE(NULL,NULL,'bbb',"BATCH_TYPE")='bbb' AND DECODE(NULL,NULL,'bbb',UPPER("BATCH_NO"))='bbb')
12 - access("PROD_CODE"="A"."PROD_CODE" AND "LOT_LINK_NO"="LOT_LINK_NO")
filter("LOT_LINK_NO"="LOT_LINK_NO" AND DECODE('feb-00',NULL,'aaa',"BATCH_TYPE")='LOT' AND DECODE('feb-00',NULL,'aaa',UPPER("BATCH_NO"))='FEB-00')
复制
如果我们看一下最先执行的步骤6，优化器认为这步访问索引 PK_DICT_BATCH_NUMBERS 只会返回18条记录，然后和 DICT_VET_PROD 进行 NESTED LOOP 连接，进行18次遍历。
但是当这个 SQL 执行后，会花费非常多时间，我们通过10046能得到原因：
STAT #3082338772 id=1 cnt=1
pid=0 pos=1 obj=0 op='NESTED LOOPS SEMI (cr=3355526 pr=12131 pw=0
time=267284869 us cost=171 size=127 card=1)'
STAT #3082338772 id=2
cnt=181405 pid=1 pos=1 obj=0 op='NESTED LOOPS SEMI (cr=2031454 pr=11445
pw=0 time=141004652 us cost=167 size=106 card=1)'
STAT #3082338772
id=3 cnt=181405 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=1419300 pr=11282
pw=0 time=73240154 us cost=164 size=85 card=1)'
STAT #3082338772
id=4 cnt=113374 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=114415 pr=1135
pw=0 time=7019253 us cost=109 size=972 card=18)'
STAT #3082338772
id=5 cnt=113374 pid=4 pos=1 obj=0 op='SORT UNIQUE (cr=860 pr=757 pw=0
time=792105 us cost=99 size=378 card=18)'
STAT #3082338772 id=6
cnt=181405 pid=5 pos=1 obj=235258 op='INDEX FAST FULL SCAN
PK_DICT_BATCH_NUMBERS (cr=860 pr=757 pw=0 time=769304 us cost=99
size=378 card=18)'
STAT #3082338772 id=7 cnt=113374 pid=4 pos=2
obj=235248 op='TABLE ACCESS BY INDEX ROWID DICT_VET_PROD (cr=113555
pr=378 pw=0 time=4089204 us cost=1 size=33 card=1)'
STAT #3082338772
id=8 cnt=113374 pid=7 pos=1 obj=235254 op='INDEX UNIQUE SCAN
PK_DICT_VET_PROD (cr=181 pr=25 pw=0 time=1399077 us cost=0 size=0
card=1)'
STAT #3082338772 id=9 cnt=181405 pid=3 pos=2 obj=235255
op='TABLE ACCESS BY INDEX ROWID DICT_BATCH_NUMBERS (cr=1304885 pr=10147
pw=0 time=65922506 us cost=4 size=31 card=1)'
STAT #3082338772 id=10
cnt=181405 pid=9 pos=1 obj=235257 op='INDEX RANGE SCAN
IDX_DICT_LOT_LINK (cr=1260282 pr=779 pw=0 time=59434165 us cost=3 size=0
card=1)'
STAT #3082338772 id=11 cnt=113374 pid=2 pos=2 obj=235258
op='INDEX RANGE SCAN PK_DICT_BATCH_NUMBERS (cr=612154 pr=163 pw=0
time=65297627 us cost=3 size=21 card=1)'
STAT #3082338772 id=12
cnt=1 pid=1 pos=2 obj=235258 op='INDEX RANGE SCAN PK_DICT_BATCH_NUMBERS
(cr=1324072 pr=686 pw=0 time=134201570 us cost=3 size=21 card=1)'
我们能发现嵌套循环（NESTED LOOP）效果很差，并不是期望的18次遍历
DICT_VET_PROD，而是113374次，两者差距了10000倍（注意执行计划里面后续的 join
方式也不是最优的）。当然，这可能是因为统计信息不准导致的，重新收集统计信息可能会解决问题，但是有时候却办不到。
Oracle
12c 引入了自适应执行计划新特性，它不是简单的继续执行原始执行计划，而是优化器发现第一步返回了 181k
条记录而不是期望的18条后，基于最新的执行结果通过调整下一步的连接方式来调整执行计划（本例就是把连接方式从嵌套循环（NESTED
LOOP）变成哈希 join（hash join））。
当在新特性下运行相同 SQL 时，尽管执行计划优化时间相同但是结果会立即返回，我们可以通过10046来看到如何做到这一点的：
STAT #47342827986896 id=0
cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS SEMI (cr=8281 pr=12181 pw=7463
time=1470339 us cost=385 size=127 card=1)'
STAT #47342827986896 id=1
cnt=1 pid=0 pos=1 obj=0 op='HASH JOIN SEMI (cr=8281 pr=12181 pw=7463
time=1471476 us cost=385 size=127 card=1)'
STAT #47342827986896 id=0 cnt=181405 pid=1 pos=1 obj=0 op='STATISTICS COLLECTOR (cr=7420 pr=11896 pw=5348 time=2126743 us)'
STAT #47342827986896 id=0 cnt=181405 pid=1 pos=1 obj=0 op='NESTED LOOPS
SEMI (cr=7420 pr=11896 pw=5348 time=1774991 us cost=382 size=106
card=1)'
STAT #47342827986896 id=2 cnt=181405 pid=1 pos=1 obj=0
op='HASH JOIN SEMI (cr=7420 pr=11896 pw=5348 time=1410241 us cost=382
size=106 card=1)'
STAT #47342827986896 id=0 cnt=181405 pid=2 pos=1 obj=0 op='STATISTICS COLLECTOR (cr=6559 pr=8641 pw=2093 time=1760891 us)'
STAT #47342827986896 id=0 cnt=181405 pid=2 pos=1 obj=0 op='NESTED LOOPS
(cr=6559 pr=8641 pw=2093 time=1336122 us cost=379 size=85 card=1)'
STAT #47342827986896 id=3 cnt=181405 pid=2 pos=1 obj=0 op='HASH JOIN
(cr=6559 pr=8641 pw=2093 time=903199 us cost=379 size=85 card=1)'
STAT #47342827986896 id=0 cnt=113374 pid=3 pos=1 obj=0 op='STATISTICS COLLECTOR (cr=1742 pr=2447 pw=713 time=1032279 us)'
STAT #47342827986896 id=0 cnt=113374 pid=3 pos=1 obj=0 op='NESTED LOOPS
(cr=1742 pr=2447 pw=713 time=769394 us cost=322 size=972 card=18)'
STAT #47342827986896 id=4 cnt=113374 pid=3 pos=1 obj=0 op='HASH JOIN
(cr=1742 pr=2447 pw=713 time=503226 us cost=322 size=972 card=18)'
STAT #47342827986896 id=0 cnt=113374 pid=4 pos=1 obj=0 op='STATISTICS COLLECTOR (cr=861 pr=1133 pw=278 time=662089 us)'
STAT #47342827986896 id=5 cnt=113374 pid=4 pos=1 obj=0 op='SORT UNIQUE
(cr=861 pr=1133 pw=278 time=394892 us cost=312 size=378 card=18)'
STAT #47342827986896 id=6 cnt=181405 pid=5 pos=1 obj=81381 op='INDEX
FAST FULL SCAN PK_DICT_BATCH_NUMBERS (cr=861 pr=855 pw=0 time=317044 us
cost=312 size=378 card=18)'
STAT #47342827986896 id=7 cnt=18780
pid=4 pos=2 obj=81371 op='TABLE ACCESS FULL DICT_VET_PROD (cr=881 pr=879
pw=0 time=48381 us cost=1 size=33 card=1)'
STAT #47342827986896
id=0 cnt=0 pid=3 pos=2 obj=81371 op='TABLE ACCESS BY INDEX ROWID BATCHED
DICT_VET_PROD (cr=0 pr=0 pw=0 time=0 us cost=1 size=33 card=1)'
STAT #47342827986896 id=0 cnt=0 pid=3 pos=1 obj=81377 op='INDEX UNIQUE
SCAN PK_DICT_VET_PROD (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)'
STAT #47342827986896 id=8 cnt=181405 pid=3 pos=2 obj=81378 op='TABLE
ACCESS FULL DICT_BATCH_NUMBERS (cr=4817 pr=4814 pw=0 time=223307 us
cost=4 size=31 card=1)'
STAT #47342827986896 id=0 cnt=0 pid=2 pos=2
obj=81378 op='TABLE ACCESS BY INDEX ROWID BATCHED DICT_BATCH_NUMBERS
(cr=0 pr=0 pw=0 time=0 us cost=4 size=31 card=1)'
STAT
#47342827986896 id=0 cnt=0 pid=2 pos=1 obj=81380 op='INDEX RANGE SCAN
IDX_DICT_LOT_LINK (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)'
STAT #47342827986896 id=9 cnt=181405 pid=2 pos=2 obj=81381 op='INDEX
FAST FULL SCAN PK_DICT_BATCH_NUMBERS (cr=861 pr=0 pw=0 time=294065 us
cost=3 size=21 card=1)'
STAT #47342827986896 id=0 cnt=0 pid=1 pos=2
obj=81381 op='INDEX RANGE SCAN PK_DICT_BATCH_NUMBERS (cr=0 pr=0 pw=0
time=0 us cost=3 size=21 card=1)'
STAT #47342827986896 id=10 cnt=1
pid=1 pos=2 obj=81381 op='INDEX FAST FULL SCAN PK_DICT_BATCH_NUMBERS
(cr=861 pr=0 pw=0 time=47182 us cost=3 size=21 card=1)'
STAT
#47342827986896 id=0 cnt=0 pid=0 pos=2 obj=81381 op='INDEX RANGE SCAN
PK_DICT_BATCH_NUMBERS (cr=0 pr=0 pw=0 time=0 us cost=3 size=21 card=1)'
从上述输出能看到，执行计划已经被调整了，在执行期间取消了嵌套循环（NESTED LOOP）变成哈希
JOIN。这种执行计划的一部分在执行期间能改变或被改变就是子计划（subplan），这种变化是通过统计信息收集器（statistics
collector）在连接过程中收集实际条数来实现的。在10046 trace 信息中我们能看到既有原始计划的 join 方式又有变化后的
join 方式。
STAT #47342827986896 id=0 cnt=113374 pid=3 pos=1 obj=0 op='NESTED LOOPS...' <-- 这个是原计划的 join 方式
STAT #47342827986896 id=4 cnt=113374 pid=3 pos=1 obj=0 op='HASH JOIN...' <-- 这个是调整后实际的 join 方式
STAT #47342827986896 id=0 cnt=113374 pid=4 pos=1 obj=0 op='STATISTICS COLLECTOR...' <-- 统计信息收集器
这种新特性可以通过修改参数 optimizer_adaptive_reporting_only 来开启和关闭：
FALSE(default) = 开启自适应执行计划特性(默认)
TRUE = 关闭这个特性（使用 11G 和之前版本的行为方式）
（这个特性也能通过参数 OPTIMIZER_ADAPTIVE_FEATURES 来关闭，这个参数是用来开启和关闭所有自适应优化器相关特性的，请参见 Oracle 官方文档）
注意：这个功能也可以在查询中使用 hint ADAPTIVE_PLAN 或者 NO_ADAPTIVE_PLAN 来启用/关闭。
Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Concepts
Chapter 1 Initialization Parameters
https://docs.oracle.com/database/121/REFRN/GUID-F5E53EFA-B395-4336-B046-1EE7AF12353B.htm
这些执行计划的改变并不会保存在数据字典中。下次这个语句再次执行时，仍然会重复上面的过程（除非 Adaptive Cursor Sharing 或者 cardinality feedback 改变了它）。这个功能仅在运行时生效。
另外，自适应执行计划并不会像 ACS 或者 cardinality feedback
一样影响连接的顺序，它仅能改变连接的方式，或者在并行执行中改变 parallel distribution 方式（在 broadcast 和
hash 两种方式中间选择）。
