Adaptive plans are one of the coolest new optimiser features in Oracle 12c. If you haven’t seen or heard about them in detail I recommend the following resources:
- Kerry Osborne’s presentation about the feature (and a link to the recording)
- Tim Hall’s article about the same feature
- Link to adaptive plans in the official documentation
- Link to the Oracle white paper
There is a caveat with this though: if your tuning script relies on pulling information from v$sql_plan and related views, you get more information than you might want. I found out about this while working on our 12c New Features training class. This, and a lot more, will be part of it. Stay tuned :)
Consider the following example. I will use the following query in this article:
SELECT /* statement002 */ /*+ gather_plan_statistics monitor */ TRUNC(order_date, 'mm'), COUNT(TRUNC(order_date, 'mm')) FROM orders o, order_items oi WHERE oi.ORDER_ID = o.order_id AND o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID = 501 AND o.ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY TRUNC(order_date, 'mm') ORDER BY 1;
These tables are part of the Swingbench SOE (order entry) schema. I have inflated the order_items table to twice its size for a total of 171,579,632 rows.
When executing this query on the x4-2 half rack in the lab I get this (sorry for the wide output!):
TRUNC(ORDER_DATE, COUNT(TRUNC(ORDER_DATE,'MM'))
----------------- -----------------------------
20120101 00:00:00 472
20120201 00:00:00 580
20120301 00:00:00 614
20120401 00:00:00 578
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT /* statement002 */ /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'), COUNT(TRUNC(order_date, 'mm')) FROM orders
o, order_items oi WHERE oi.ORDER_ID = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1
Plan hash value: 812470616
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29766 (100)| | | 4 |00:00:00.06 | 29354 | 17 | | | |
| 1 | SORT ORDER BY | | 1 | 404 | 14948 | 29766 (1)| | | 4 |00:00:00.06 | 29354 | 17 | 2048 | 2048 | 2048 (0)|
| 2 | HASH GROUP BY | | 1 | 404 | 14948 | 29766 (1)| | | 4 |00:00:00.06 | 29354 | 17 | 1394K| 1394K| 634K (0)|
|* 3 | FILTER | | 1 | | | | | | 2244 |00:00:00.05 | 29354 | 17 | | | |
| 4 | NESTED LOOPS | | 1 | 2996 | 108K| 29764 (1)| | | 2244 |00:00:00.05 | 29354 | 17 | | | |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 1 | 434 | 13454 | 28462 (1)| ROWID | ROWID | 362 |00:00:00.03 | 28261 | 17 | | | |
|* 6 | INDEX RANGE SCAN | ORD_WAREHOUSE_IX | 1 | 28624 | | 90 (0)| | | 28441 |00:00:00.02 | 90 | 0 | 1025K| 1025K| |
|* 7 | INDEX RANGE SCAN | ITEM_ORDER_IX | 362 | 7 | 42 | 3 (0)| | | 2244 |00:00:00.01 | 1093 | 0 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TIMESTAMP' 2012-07-01 00:00:00'>=TIMESTAMP' 2012-01-01 00:00:00')
5 - filter(("O"."CUSTOMER_CLASS"='Prime' AND "O"."ORDER_DATE">=TIMESTAMP' 2012-01-01 00:00:00' AND "O"."ORDER_DATE"<=TIMESTAMP' 2012-07-01 00:00:00'))
6 - access("O"."WAREHOUSE_ID"=501)
7 - access("OI"."ORDER_ID"="O"."ORDER_ID")
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 2 Sql Plan Directives used for this statement
@x is one of the many useful scripts written by Tanel Poder. I recommend you download the tpt_public.zip and get familiar with what they do-they are just great.
The Execution Plan
Please take a note at the output of the execution plan. The query is an adaptive plan (see notes). The join between orders and order_items is performed using a nested loop (step 4 and the following ones). You can also use v$sql to show that this plan is an adaptive plan:
SQL> select sql_id, child_number, executions, is_reoptimizable, is_resolved_adaptive_plan 2 from v$sql where sql_id = '3wb68w2kj6gdd'; SQL_ID CHILD_NUMBER EXECUTIONS I I ------------- ------------ ---------- - - 3wb68w2kj6gdd 0 1 Y Y
It is indeed already resolved. Using Jonathan Lewis’s recent notes on reoptimisation I checked what the optimiser worked out:
SQL> select sql_id, child_number, hint_id, hint_text, reparse
2 from v$sql_reoptimization_hints where sql_id = '3wb68w2kj6gdd' and child_number = 0;
SQL_ID CHILD_NUMBER HINT_ID HINT_TEXT REPARSE
------------- ------------ ---------- -------------------------------------------------- ----------
3wb68w2kj6gdd 0 1 OPT_ESTIMATE (@"SEL$1" GROUP_BY ROWS=4.000000 ) 1
3wb68w2kj6gdd 0 2 OPT_ESTIMATE (@"SEL$1" JOIN ("OI"@"SEL$1" "O"@"SEL 1
$1") ROWS=1.000000 )
Nice! That also provides some insights that could be useful. You can map the hint_text to v$sql_plan.object_alias, it is the query block name. I have actually executed slight variations of the query in preparation …
Back to my problem
In the past I have used v$sql_plan (indirectly, via scripts written by far more clever people than me) to work each step in the plan. v$sql_plan has an ID, PARENT_ID and DEPTH that make it easier to work out where in the plan you are. Using my old approach I got stuck, consider this:
SQL> r
1 select
2 lpad(' ',sp.depth*1,' ')
3 || sp.operation AS operation,
4 sp.OPTIONS,
5 sp.object#,
6 sp.object_name,
7 sp.object_alias,
8 sp.object_type
9 FROM v$sql_plan sp
10* where sql_id = '3wb68w2kj6gdd' and child_number = 0
OPERATION OPTIONS OBJECT# OBJECT_NAME OBJECT_ALI OBJECT_TYP
---------------------------------------- ----------------------------------- ---------- ------------------------- ---------- ----------
SELECT STATEMENT
SORT ORDER BY
HASH GROUP BY
FILTER
HASH JOIN
NESTED LOOPS
STATISTICS COLLECTOR
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED 28865 ORDERS O@SEL$1 TABLE
INDEX RANGE SCAN 29329 ORD_WAREHOUSE_IX O@SEL$1 INDEX
INDEX RANGE SCAN 29302 ITEM_ORDER_IX OI@SEL$1 INDEX
INDEX FAST FULL SCAN 29302 ITEM_ORDER_IX OI@SEL$1 INDEX
11 rows selected.
If you compare this with the DBMS_XPLAN-output from above then you notice there is a lot more information in the query against v$sql_plan …In fact, that’s the same output as what you get when calling DBMS_XPLAN.DISPLAY_CURSOR(… format => ‘ADAPTIVE’):
SQL> select * from table(dbms_xplan.display_cursor('3wb68w2kj6gdd',0,'+ADAPTIVE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT /* statement002 */ /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'), COUNT(TRUNC(order_date, 'mm')) FROM orders
o, order_items oi WHERE oi.ORDER_ID = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1
Plan hash value: 812470616
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29766 (100)| | | |
| 1 | SORT ORDER BY | | 404 | 14948 | 29766 (1)| 00:00:02 | | |
| 2 | HASH GROUP BY | | 404 | 14948 | 29766 (1)| 00:00:02 | | |
| * 3 | FILTER | | | | | | | |
|- * 4 | HASH JOIN | | 2996 | 108K| 29764 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 2996 | 108K| 29764 (1)| 00:00:02 | | |
|- 6 | STATISTICS COLLECTOR | | | | | | | |
| * 7 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 434 | 13454 | 28462 (1)| 00:00:02 | ROWID | ROWID |
| * 8 | INDEX RANGE SCAN | ORD_WAREHOUSE_IX | 28624 | | 90 (0)| 00:00:01 | | |
| * 9 | INDEX RANGE SCAN | ITEM_ORDER_IX | 7 | 42 | 3 (0)| 00:00:01 | | |
|- 10 | INDEX FAST FULL SCAN | ITEM_ORDER_IX | 7 | 42 | 3 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
...
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan (rows marked '-' are inactive)
- 2 Sql Plan Directives used for this statement
Note the line: this is an adaptive plan (rows marked ‘-‘ are inactive). But how does DBMS_XPLAN know that these lines are hidden? There doesn’t seem to be a view v$sql_plan_hidden_lines. I tried a few things and eventually traced the call to DBMS_XPLAN.DISPLAY_CURSOR. In the trace I found the trick Oracle uses:
===================== PARSING IN CURSOR #140127730386096 len=298 dep=2 uid=75 oct=3 lid=75 tim=767822822383 hv=580989905 ad='e9e6f7570' sqlid='fg4skgcja2cyj' SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'), EXTRACTVALUE(VALUE(D), '/row/@par'), EXTRACTVALUE(VALUE(D), '/row/@prt'), EXTRACTVALUE(VALUE(D), '/row/@dep'), EXTRACTVALUE(VALUE(D), '/row/@skp') FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:B1 ), '/*/display_map/row'))) D END OF STMT ... STAT #140127732564416 id=1 cnt=11 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=691 us cost=1 size=8935 card=1)' STAT #140127732564416 id=2 cnt=11 pid=1 pos=1 obj=0 op='VIEW (cr=0 pr=0 pw=0 time=1223 us cost=0 size=8935 card=1)' STAT #140127732564416 id=3 cnt=11 pid=2 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=1116 us cost=0 size=957 card=1)' STAT #140127732564416 id=4 cnt=11 pid=3 pos=1 obj=0 op='FIXED TABLE FIXED INDEX X$KQLFXPL (ind:4) (cr=0 pr=0 pw=0 time=902 us cost=0 size=853 card=1)' STAT #140127732564416 id=5 cnt=11 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=134 us cost=0 size=104 card=1)'
So that’s it! The X$ views map to v$sql and v$sql_plan unless I am very mistaken. V$SQL_PLAN only has 1 column that contains XML-other_xml. Using this information I thought there has to be something in there … and indeed, there is:
SQL> select xmltype(other_xml)
2 from v$sql_plan
3 where sql_id = '3wb68w2kj6gdd' and child_number = 0
4 and other_xml is not null;
XMLTYPE(OTHER_XML)
---------------------------------------------------------------------------------------------------------------------------
<other_xml>
<info type="db_version">12.1.0.2</info>
<info type="parse_schema"><![CDATA["SOE"]]></info>
<info type="dynamic_sampling" note="y">2</info>
<info type="plan_hash_full">166760258</info>
<info type="plan_hash">812470616</info>
<info type="plan_hash_2">3729130925</info>
<info type="adaptive_plan" note="y">yes</info>
<spd>
<cv>0</cv>
<cu>2</cu>
</spd>
<outline_data>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.1.0.2')]]></hint>
<hint><![CDATA[DB_VERSION('12.1.0.2')]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("ORDERS"."WAREHOUSE_ID" "ORDERS"."ORDER_STATUS"))]]></hint>
<hint><![CDATA[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "O"@"SEL$1")]]></hint>
<hint><![CDATA[INDEX(@"SEL$1" "OI"@"SEL$1" ("ORDER_ITEMS"."ORDER_ID"))]]></hint>
<hint><![CDATA[LEADING(@"SEL$1" "O"@"SEL$1" "OI"@"SEL$1")]]></hint>
<hint><![CDATA[USE_NL(@"SEL$1" "OI"@"SEL$1")]]></hint>
<hint><![CDATA[USE_HASH_AGGREGATION(@"SEL$1")]]></hint>
</outline_data>
<display_map>
<row op="1" dis="1" par="0" prt="0" dep="1" skp="0"/>
<row op="2" dis="2" par="1" prt="0" dep="2" skp="0"/>
<row op="3" dis="3" par="2" prt="0" dep="3" skp="0"/>
<row op="4" dis="3" par="3" prt="0" dep="3" skp="1"/>
<row op="5" dis="4" par="3" prt="0" dep="4" skp="0"/>
<row op="6" dis="4" par="4" prt="0" dep="4" skp="1"/>
<row op="7" dis="5" par="4" prt="5" dep="5" skp="0"/>
<row op="8" dis="6" par="5" prt="0" dep="6" skp="0"/>
<row op="9" dis="7" par="4" prt="0" dep="5" skp="0"/>
<row op="10" dis="7" par="3" prt="0" dep="3" skp="1"/>
</display_map>
</other_xml>
The SQL statement from the trace was not much use to me, the following seemed better suited to work out what was happening. I added what I think the abbreviations stand for:
WITH display_map AS
(SELECT X.*
FROM v$sql_plan,
XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS
op NUMBER PATH '@op', -- operation
dis NUMBER PATH '@dis', -- display
par NUMBER PATH '@par', -- parent
prt NUMBER PATH '@prt', -- ?
dep NUMBER PATH '@dep', -- depth
skp NUMBER PATH '@skp' ) -- skip
AS X
WHERE sql_id = '&sql_id'
AND child_number = &sql_child
AND other_xml IS NOT NULL
)
SELECT * from display_map;
Enter value for sql_id: 3wb68w2kj6gdd
Enter value for sql_child: 0
OP DIS PAR PRT DEP SKP
---------- ---------- ---------- ---------- ---------- ----------
1 1 0 0 1 0
2 2 1 0 2 0
3 3 2 0 3 0
4 3 3 0 3 1
5 4 3 0 4 0
6 4 4 0 4 1
7 5 4 5 5 0
8 6 5 0 6 0
9 7 4 0 5 0
10 7 3 0 3 1
10 rows selected.
Well-that’s a starting point. Now all I have to do is join the display map to v$sql_plan cleverly. After a little bit of fiddling with the query this seems to work:
WITH display_map AS
(SELECT X.*
FROM v$sql_plan,
XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS
op NUMBER PATH '@op', -- operation
dis NUMBER PATH '@dis', -- display
par NUMBER PATH '@par', -- parent
prt NUMBER PATH '@prt', -- ?
dep NUMBER PATH '@dep', -- depth
skp NUMBER PATH '@skp' ) -- skip
AS X
WHERE sql_id = '&sql_id'
AND child_number = &sql_child
AND other_xml IS NOT NULL
)
SELECT
-- new ID, depth, parent etc from display_map
NVL(m.dis, 0) AS new_id,
m.par AS new_parent,
m.dep AS new_depth,
-- plan formatting, as usual
lpad(' ',m.dep*1,' ')
|| sp.operation AS operation,
sp.OPTIONS,
sp.object#,
sp.object_name,
sp.object_alias,
sp.object_type
FROM v$sql_plan sp
LEFT OUTER JOIN display_map m
ON (sp.id = m.op)
WHERE sp.sql_Id = '&sql_id'
AND sp.child_number = &sql_child
AND NVL(m.skp,0) <> 1
ORDER BY NVL(dis,0);
NEW_ID NEW_PARENT NEW_DEPTH OPERATION OPTIONS OBJECT# OBJECT_NAME OBJECT_ALI OBJECT_TYP
---------- ---------- ---------- ------------------------------ ----------------------------------- ---------- ------------------------- ---------- ----------
0 SELECT STATEMENT
1 0 1 SORT ORDER BY
2 1 2 HASH GROUP BY
3 2 3 FILTER
4 3 4 NESTED LOOPS
5 4 5 TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED 28865 ORDERS O@SEL$1 TABLE
6 5 6 INDEX RANGE SCAN 29329 ORD_WAREHOUSE_IX O@SEL$1 INDEX
7 4 5 INDEX RANGE SCAN 29302 ITEM_ORDER_IX OI@SEL$1 INDEX
8 rows selected.
This seems to match what Oracle produces:
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 3wb68w2kj6gdd, child number 0 ------------------------------------- SELECT /* statement002 */ /*+ gather_plan_statistics monitor */ TRUNC(order_date, 'mm'), COUNT(TRUNC(order_date, 'mm')) FROM orders o, order_items oi WHERE oi.ORDER_ID = o.order_id AND o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID = 501 AND o.ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY TRUNC(order_date, 'mm') ORDER BY 1 Plan hash value: 812470616 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29766 (100)| | | | | 1 | SORT ORDER BY | | 404 | 14948 | 29766 (1)| 00:00:02 | | | | 2 | HASH GROUP BY | | 404 | 14948 | 29766 (1)| 00:00:02 | | | |* 3 | FILTER | | | | | | | | | 4 | NESTED LOOPS | | 2996 | 108K| 29764 (1)| 00:00:02 | | | |* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS | 434 | 13454 | 28462 (1)| 00:00:02 | ROWID | ROWID | |* 6 | INDEX RANGE SCAN | ORD_WAREHOUSE_IX | 28624 | | 90 (0)| 00:00:01 | | | |* 7 | INDEX RANGE SCAN | ITEM_ORDER_IX | 7 | 42 | 3 (0)| 00:00:01 | | | -----------------------------------------------------------------------------------------------------------------------------------
With this done it should be possible to add other diagnostic information too, just join the additional views and add the relevant columns. Hope this helps! I haven’t performed extensive testing on the approach but wanted to put it out here for the more clever people to tell me where I’m wrong.
Hi,
I have some questions about the adaptive execution plans in your article:
1) what does the “other_xml is not null” mean for?
2) Does the “other_xml is not null” is only for the execution plan which oracle used?
thx, wait for your reply.
Hello,
thanks for passing by. I wanted to “pretty print” the output of the other_xml column. I find the cast to xmltype() quite useful for this purpose. However, when I wrote my queries initially I run into issues:
1) if other_xml is null, the xmltype() function will fail with a PL/SQL: numeric or value error. There are 8 rows for this SQL_ID in v$sql_plan, and for 7 of them other_xml is null
2) there is only 1 other_xml column populated for the combination of SQL_ID and child_number
Hope this helps
OK, got it !
Thanks Martin !