Oracle: DBMS_XPLAN.DISPLAY

Parameters

dbms_xplan.display_cursor has three optional parameters:

`sql_id`
`child_number`
`format`

If display_cursor is executed without specifying any arguments, it formats the execution plan of the last SQL statement that was executed in the session.

In order to display an arbitrary SQL statement that is found in v$sql_plan, the SQL statement's sql_id and child_number must be provided as arguments to the function

Example

Execute an SQL statement

An SQL statement is executed.

In order to collect execution statistics for the SQL plan, the hint gather_plan_statistics is used.

The SQL statement also has the text find-this-statement-01 in a comment. This text will be used to find the statement's sql_id and child number whose values are used in dbms_xplan.display_cursor.

select
--+  gather_plan_statistics
--   find-this-statement-01
     count(*)       cnt
from
     dba_objects    a,
     dba_objects    b
where
     a.created     > b.created      and
     a.object_name > b.object_name
;

Find SQL_ID and child number of executed SQL statement

select
   sql_id,
   child_number,
   sql_text 
from
   v$sql
where
   sql_text     like '%find-this-statement-01%' and
   sql_text not like '%v$sql%';

Execute DBMS_XPLAN.DISPLAY_CURSOR

With the sql_id and child number queried in the previous statement, we can now execute dbms_xplan.display_cursor:

select
   *
from
   table(dbms_xplan.display_cursor(
           sql_id          => '1cb036z54s4hu',
           cursor_child_no =>  0,
           format          => 'ALLSTATS LAST'
        ));

Data being used for DBMS_XPLAN.DISPLAY_CURSOR

The data that is formatted by dbms_xplan.display_cursor is found in v$sql_plan_statistics_all, v$sql, v$sql_plan and v$sql_shard.

Slim report: showing starts and actual rows only

Adding rowstats to the format parameter shows actual rows (A-Rows) and expected rows (E-Rows). The -rows instructs the to remove the E-Rows column from the report.

select /*+ monitor */
   dummy
from
   dual;

select * from table(dbms_xplan.display_cursor(format => 'rowstats -rows last'));
--
-- ----------------------------------------------------
-- | Id  | Operation         | Name | Starts | A-Rows |
-- ----------------------------------------------------
-- |   0 | SELECT STATEMENT  |      |      1 |      1 |
-- |   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |
-- ----------------------------------------------------

Warning: basic plan statistics not available

set serveroutput off

select * from dual where dummy = 'FOO';

select * from table(dbms_xplan.display_cursor(format => 'allstats last'));
--
-- -------------------------------------------
-- | Id  | Operation         | Name | E-Rows |
-- -------------------------------------------
-- |   0 | SELECT STATEMENT  |      |        |
-- |*  1 |  TABLE ACCESS FULL| DUAL |      1 |
-- -------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter("DUMMY"='FOO')
--  
-- Note
-- -----
--    - Warning: basic plan statistics not available. These are only collected when:
--        * hint 'gather_plan_statistics' is used for the statement or
--        * parameter 'statistics_level' is set to 'ALL', at session or system level

 
select /*+ gather_plan_statistics */
   *
from dual where dummy = 'FOO';

select * from table(dbms_xplan.display_cursor(format => 'allstats last'));
--
------------------------------------------------------------------------------------
-- | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-- ------------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT  |      |      1 |        |      0 |00:00:00.01 |       2 |
-- |*  1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      0 |00:00:00.01 |       2 |
-- ------------------------------------------------------------------------------------
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter("DUMMY"='FOO')

Oracle: DBMS_XPLAN.DISPLAY_CURSOR