Adaptive plans and v$sql_plan and related views

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:

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s