Differences

This shows you the differences between two versions of the page.

--- adaptiveplans [2015/07/22 13:23] – z0hpvk
+++ adaptiveplans [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ===== Adaptive Plans =====
 Adaptive Plans in Oracle Database 12c allow runtime changes to execution plans.\\
-At runtime the cardinality of operations is checked using statistics collectors 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.
+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
+<code>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));</code>
+Use the following to also see the expected and actual cardinalities.\\
+You will also need to add the GATHER_PLAN_STATISTICS hint to the SQL query.
+<code>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));</code>
+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 null, Y or N\\
+  * 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.