Adaptive plans and v$sql_plan and related views
Posted by Martin Bach on January 13, 2015
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.
Leave a Reply