Oracle Adaptive Plans新特性的个人理解

--------------------------------------------------------------------------------
| Id| Operation                    |Name             |Rows|Bytes|Cost %CPU|Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                 | 10| 250| 3 (0)| 00:00:01|
| 1 |  NESTED LOOPS                |                 |   |    |      |         |
| 2 |   NESTED LOOPS               |                 | 10| 250| 3 (0)| 00:00:01|
|*3 |    TABLE ACCESS FULL         |DEPARTMENTS      |  1|   7| 2 (0)| 00:00:01|
|*4 |    INDEX RANGE SCAN          |EMP_DEPARTMENT_IX| 10|    | 0 (0)| 00:00:01|
| 5 |   TABLE ACCESS BY INDEX ROWID|EMPLOYEES        | 10| 180| 1 (0)| 00:00:01|

Purpose of Adaptive Plans
The ability of the optimizer to adapt a plan, based on information learned during execution, can greatly improve query performance. 
Adaptive plans are useful because the optimizer occasionally picks a suboptimal default plan because of a cardinality misestimate. 
The ability to adapt the plan at run time based on actual execution statistics results in a more optimal final plan. 
After choosing the final plan, the optimizer uses it for subsequent executions, thus ensuring that the suboptimal plan is not reused.

How Adaptive Plans Work
An adaptive plan contains multiple predetermined subplans, and an optimizer statistics collector. 
A subplan is a portion of a plan that the optimizer can switch to as an alternative at run time. 
For example, a nested loops join could be switched to a hash join during execution.
 An optimizer statistics collector is a row source inserted into a plan at key points to collect run-time statistics.
 These statistics help the optimizer make a final decision between multiple subplans.
 
During statement execution, the statistics collector gathers information about the execution, and buffers some rows received by the subplan. 
Based on the information observed by the collector, the optimizer chooses a subplan. 
At this point, the collector stops collecting statistics and buffering rows, and permits rows to pass through instead. 
On subsequent executions of the child cursor, the optimizer continues to use the same plan unless the plan ages out of the cache,
 or a different optimizer feature (for example, adaptive cursor sharing or statistics feedback) invalidates the plan.

与本特性有关的数据库初始化参数:
OPTIMIZER_FEATURES_ENABLE is 12.1.0.1 or later
OPTIMIZER_ADAPTIVE_REPORTING_ONLY initialization parameter is set to the default of false
OPTIMIZER_ADAPTIVE_FEATURES  enables

------------------------------------------------------------------------------------------------
|Id | Operation          | Name         |Starts|E-Rows|A-Rows|A-Time|Buff|Reads|OMem|1Mem|O/1/M|
------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT   |                     |1| | 13|00:00:00.10 |21 |17 |      |      |     |
|*1|  HASH JOIN         |                     |1|4| 13|00:00:00.10 |21 |17 | 2061K| 2061K|1/0/0|
|*2|   TABLE ACCESS FULL| ORDER_ITEMS         |1|4| 13|00:00:00.07 | 5 | 4 |      |      |     |
| 3|   TABLE ACCESS FULL| PRODUCT_INFORMATION |1|1|288|00:00:00.03 |16 |13 |      |      |     |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Note
-----
   - this is an adaptive plan     -------------->>>>>>>>>>>>>>>>注意此处!!!

set lines 300
alter session set statistics_level=all;
select * from t1 where id=88 and n=88;
select * from table(dbms_xplan.display_cursor(null,null,'advanced ALLSTATS LAST'));