Based on the available statistics on the objects, the Oracle optimizer could incorrectly estimate the cardinality of certain row sources, resulting in a sub-optimal plan. Adaptive plans are a technique used by the Oracle 12c optimizer to adapt a plan, based on information learned during execution, to pick a better plan.
For eg: If the optimizer estimated the where clause item_id = 20, is going to generate 10 rows and in reality if there were 100,000 rows in the table with item_id = 20, the optimizer might have chosen a nested loops join to this table. With adaptive plans, the optimizer gets the opportunity to switch this nested loops to a hash join, during the execution of this statement.
When the sql statement is parsed, the optimizer creates what is called a “default plan”. If there are some incorrect cardinality estimates performed by the optimizer, then it is probable that it has picked an execution plan with wrong join methods. An adaptive plan, contains multiple pre-determined “Sub plan’s”. A subplan is a portion of the plan that the optimizer can switch to as an alternative at runtime. The optimizer cannot change the whole execution plan at Execution time, it can adapt portions of it.
There are multiple “Statistics Collector’s”, inserted as rowsource’s, at key points in the execution plan.
During execution the statistics collector buffers some rows received by the subplan. Based on the information observed by the collector, the optimizer chooses a specific subplan.
Based on the observations of the statistics collectors a “Final plan” is then chosen by the optimizer and executed. After the first execution, any subsequent execution of this statement, uses this “final plan”. ie it does not incur the overhead of the statistics collection at execution time.
Let us take a look at the following sql statement and its execution plan.
SELECT product_name
FROM order_items o, product_information p
WHERE o.unit_price = 15
AND quantity > 1
AND p.product_id = o.product_id
/
This is a sql where the oracle 12c optimizer, decides to use Adaptive optimization.
If we use dbms_xplan.display_cursor to show the execution plan, it shows the following. This is the “final plan”
SQL_ID 971cdqusn06z9, child number 0
-------------------------------------
SELECT product_name FROM order_items o, product_information p
WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id =
o.product_id
Plan hash value: 1553478007
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | HASH JOIN | | 4 | 128 | 7 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ORDER_ITEMS | 4 | 48 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "P"@"SEL$1")
USE_HASH(@"SEL$1" "P"@"SEL$1")
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
Note
-----
- this is an adaptive plan
Since the Notes section says that this is an adaptive plan, we can use dbms_xplan to show the “full plan”, which includes the “default plan” and the “sub plan’s”
We have to use the ADAPTIVE keyword in dbms_xplan to display the ‘full plan’.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('971cdqusn06z9',0,'ALLSTATS LAST +ADAPTIVE'))
SQL_ID 971cdqusn06z9, child number 0
-------------------------------------
SELECT product_name FROM order_items o, product_information p
WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id =
o.product_id
Plan hash value: 1553478007
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.01 | 24 | 20 | | | |
| * 1 | HASH JOIN | | 1 | 4 | 13 |00:00:00.01 | 24 | 20 | 2061K| 2061K| 445K (0)|
|- 2 | NESTED LOOPS | | 1 | 4 | 13 |00:00:00.01 | 7 | 6 | | | |
|- 3 | NESTED LOOPS | | 1 | 4 | 13 |00:00:00.01 | 7 | 6 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 13 |00:00:00.01 | 7 | 6 | | | |
| * 5 | TABLE ACCESS FULL | ORDER_ITEMS | 1 | 4 | 13 |00:00:00.01 | 7 | 6 | | | |
|- * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|- 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 8 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 1 | 288 |00:00:00.01 | 17 | 14 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
In the plan we can see the statistics collector row source. We can see that the Index access “Access Method” was evaluated and discarded by the optimizer. You can see that the optimizer estimated 1 row from product_information and actually it returned 288 rows. The optimizer calcluates an inflection point after which the nested loops operation becomes less efficient and chooses the full scans followed by the hash join.
As indicated in the Note section, the lines that have a – at the beginning of the line, are inactive in the “Final Plan”.
Let us check the flags from v$sql
select is_reoptimizable,is_resolved_adaptive_plan
from v$sql where
sql_id = '971cdqusn06z9'
I I
- -
Y Y
The output indicates that the statement is re-optimizable and that it was resolved by using an adaptive plan.
Every step in the ‘full plan’ is stored in v$sql_plan, the information regarding which steps are ‘on’ or ‘off’ in the ‘final plan’ is stored in the column other_xml, under the xml element, display_map.
This is how it looks for sqlid 971cdqusn06z9
The op= property in the xml, maps to the id column in v$sql_plan (ie the step number). The skp= property indicates whether the step is active in the final plan or not. A value of 1 indicates that, that row is skipped in the final plan. You can display it in a row format with the following sql
select * from
(SELECT dispmap.*
FROM v$sql_plan,
XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
op NUMBER PATH '@op',
dis NUMBER PATH '@dis',
par NUMBER PATH '@par',
prt NUMBER PATH '@prt',
dep NUMBER PATH '@dep',
skp NUMBER PATH '@skp' )
AS dispmap
WHERE sql_id = '&sql_id'
AND child_number = &sql_child
AND other_xml IS NOT NULL
)
/
OP DIS PAR PRT DEP SKP
---------- ---------- ---------- ---------- ---------- ----------
1 1 0 0 1 0
2 1 1 0 1 1
3 1 1 0 1 1
4 1 1 0 1 1
5 2 1 0 2 0
6 2 1 0 1 1
7 2 1 0 1 1
8 3 1 0 2 0
This output can be joined with v$sql_plan to produce an output of the “final plan” .
SELECT
NVL(map.dis,0) id,
map.par parent,
map.dep depth,
lpad(' ',map.dep*1,' ')
|| sp.operation AS operation,
sp.OPTIONS,
sp.object#,
sp.object_name
FROM v$sql_plan sp,
(SELECT dispmap.*
FROM v$sql_plan,
XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml) COLUMNS
op NUMBER PATH '@op',
dis NUMBER PATH '@dis',
par NUMBER PATH '@par',
prt NUMBER PATH '@prt',
dep NUMBER PATH '@dep',
skp NUMBER PATH '@skp' )
AS dispmap
WHERE sql_id = '&sql_id'
AND child_number = &sql_child
AND other_xml is not null
) map
WHERE sp.id = map.op(+)
AND sp.sql_Id = '&sql_id'
AND sp.child_number = &sql_child
AND nvl(map.skp,0) != 1
ORDER BY nvl(map.dis,0)
If you look at the 10053 trace for the sql, you can see how the optimizer calculates an inflection point.
References
https://martincarstenbach.wordpress.com/2015/01/13/adaptive-plans-and-vsql_plan-and-related-views/