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 !