Friday, January 4, 2013

Undocumented parameter to DBMS_XPLAN.DISPLAY_CURSOR

The documentation
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786

tops out at

format=>'ALL'


DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id           IN  VARCHAR2  DEFAULT  NULL,
   cursor_child_no  IN  NUMBER    DEFAULT  0, 
   format           IN  VARCHAR2  DEFAULT  'TYPICAL');


What if we wanted MORE ... 

First let's see what we get with 'ALL' 

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ALL'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


32 rows selected.


OK all as expected. 

What if we changed 'ALL' to 'ADVANCED' ?

set long 32000
set linesize 32000
set pagesize 0
-- set autotrace on
alter system set result_cache_mode=manual;
--select cust_first_name,cust_last_name from customers where cust_id=103379;
-- set autotrace off
select cust_first_name,cust_last_name from customers where cust_id=103379;
select * from table(dbms_xplan.display_cursor(format=>'ADVANCED'));
quit;

System altered.

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0
-------------------------------------
select cust_first_name,cust_last_name from customers where
cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - SEL$1 / CUSTOMERS@SEL$1
   2 - SEL$1 / CUSTOMERS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "CUSTOMERS"@"SEL$1" ("CUSTOMERS"."CUST_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_ID"=103379)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "CUST_FIRST_NAME"[VARCHAR2,20], "CUST_LAST_NAME"[VARCHAR2,40]
   2 - "CUSTOMERS".ROWID[ROWID,10]


46 rows selected.

! Look at the extra information provided.    Have a good one ! 

No comments: