This is an old revision of the document!
Adaptive Plans
Adaptive Plans in Oracle Database 12c allow runtime changes to execution plans.
At runtime the cardinality of operations is checked and compared to the cardinality estimates used to generate the execution plan. If the cardinality of the operation is not as expected, an alternative subplan can be used.
To view the whole adaptive plan run the following after your SQL statement
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));
Use the following to also see the expected and actual cardinalities.
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
With an adaptive plan you may see differences between the estimated rows (E-Rows) and actual rows (A-Rows).
The v$SQL view has a new column called IS_RESOLVED_ADAPTIVE_PLAN.
This column can have a value of N, Y or null.
- Null : Non-Adapative Plan
- Y : Adaptive Plan and a final plan has been selected
- N : Adaptive Plan but the final plan has not yet been decided on
Initialisation Parameter: OPTIMIZER_ADAPTIVE_REPORTING_ONLY (Default value FALSE).
When set to TRUE information needed to enable adaptive join methods will be gathered only. No action is taken to change the plan.