Advanced Format option in DISPLAY_CURSOR

Oracle is good at sneaking things it to the database.  And the Advanced format option for DISPLAY CURSOR is one of those things.   Using by itself you get the Basic plan, the Alias information,  The Outline data,  Predicate information, column projection information and notes. 

The cool stuff is the outline data.  For those of us who have been around Oracle for a while this is the old “stored outline” which is the set of hints that would make a sorted outline.  Today these are the hints used to make a baseline.  They can just be cool to read and see what the hints would be to create the plan and to give you some insight into the hints themselves.

Here a quick example.  First the little script I used in SQLcl:  (Yea I’m moving to SQLcl, really a great tool.  A big step up for out command line tool, SQL*Plus is still great and a wonderful tool, SQLcl take all the greatness of SQL*Plus and adds some great functionality.)

set serveroutput off

create or replace view orderview as

select /*+ qb_name(orderview) */

  ord2.cust_no custnum, ord2.order_no ordnum,

  order_date orddate, product_id prodid, quantity

from ord_item2, ord2

where ord_item2.order_no = ord2.order_no

/

set termout off

select /*+ qb_name(mainblock) */lastname, custnum, ordnum, orddate, prodid, quantity

from customer2 c2, orderview ov

where ov.custnum = c2.cust_no;

set termout on

select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));

 Here’s the run:

************************************

SQL> @rvd02

PLAN_TABLE_OUTPUT

SQL_ID  bsu4vh744stwq, child number 0

-------------------------------------

select /*+ qb_name(mainblock) */lastname, custnum, ordnum, orddate,

prodid, quantity from customer2 c2, orderview ov  where ov.custnum =

c2.cust_no

Plan hash value: 4000018549

---------------------------------------------------------------------------------

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |           |       |       |   118 (100)|          |

|*  1 |  HASH JOIN          |           | 70975 |  3881K|   118   (1)| 00:00:01 |

|*  2 |   HASH JOIN         |           | 12890 |   553K|    51   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| CUSTOMER2 |  1000 | 26000 |     7   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| ORD2      | 12890 |   226K|    44   (0)| 00:00:01 |

|   5 |   TABLE ACCESS FULL | ORD_ITEM2 | 70975 |   831K|    66   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$19724973

   3 - SEL$19724973 / C2@MAINBLOCK

   4 - SEL$19724973 / ORD2@ORDERVIEW

   5 - SEL$19724973 / ORD_ITEM2@ORDERVIEW

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

      DB_VERSION('12.1.0.2')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$19724973")

      MERGE(@"ORDERVIEW")

      OUTLINE(@"MAINBLOCK")

      OUTLINE(@"ORDERVIEW")

      FULL(@"SEL$19724973" "C2"@"MAINBLOCK")

      FULL(@"SEL$19724973" "ORD2"@"ORDERVIEW")

      FULL(@"SEL$19724973" "ORD_ITEM2"@"ORDERVIEW")

      LEADING(@"SEL$19724973" "C2"@"MAINBLOCK" "ORD2"@"ORDERVIEW"

              "ORD_ITEM2"@"ORDERVIEW")

      USE_HASH(@"SEL$19724973" "ORD2"@"ORDERVIEW")

      USE_HASH(@"SEL$19724973" "ORD_ITEM2"@"ORDERVIEW")

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("ORD_ITEM2"."ORDER_NO"="ORD2"."ORDER_NO")

   2 - access("ORD2"."CUST_NO"="C2"."CUST_NO")

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=1) "ORD2"."ORDER_NO"[NUMBER,22],

       "ORD2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20],

       "QUANTITY"[NUMBER,22], "ORDER_DATE"[DATE,7], "PRODUCT_ID"[NUMBER,22],

       "QUANTITY"[NUMBER,22], "PRODUCT_ID"[NUMBER,22]

   2 - (#keys=1) "ORD2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20],

       "ORD2"."ORDER_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]

   3 - (rowset=200) "C2"."CUST_NO"[NUMBER,22], "LASTNAME"[VARCHAR2,20]

   4 - (rowset=200) "ORD2"."ORDER_NO"[NUMBER,22],

       "ORD2"."CUST_NO"[NUMBER,22], "ORDER_DATE"[DATE,7]

   5 - "ORD_ITEM2"."ORDER_NO"[NUMBER,22], "PRODUCT_ID"[NUMBER,22],

       "QUANTITY"[NUMBER,22]

Note

-----

   - this is an adaptive plan

SQL>