Martins Blog

Trying to explain complex things in simple terms

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:

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

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

 
%d bloggers like this: