I have in the past struggled with the myriad of formatting options available with dbms_xplan.display_cursor so I was pleased to learn about a quick and easy to remember way of formatting the results at this years UKOUG conference.

First execute the SQL statement of interest including the gather_plan_statistics hint:

SELECT /*+ gather_plan_statistics */ e.*
FROM   hr.employees e
WHERE  e.first_name = 'John'
AND    e.last_name  = 'Chen'
/

Then run dbms_xplan.display_cursor:

SELECT *
FROM   table(dbms_xplan.display_cursor(format => 'allstats last'))
/

The key point is that ‘allstats last’ is used to format the output. This is a short cut for the formatting options of ‘IOSTATS MEMSTATS’. The inclusion of last ensures that the details of the last SQL statement executed is shown.

The example shown above will work on 11g R1 onwards because of the use of a named parameter in SQL. If you are using 10g you need to remove the change the call dbms_xplan.display_cursor to dbms_xplan.display_cursor(null, null, ‘allstats last’))

Once run you will see the output of:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  abd665w8zn3f8, child number 0                                                                                                                                                                                                                                            
-------------------------------------                                                                                                                                                                                                                                                              
SELECT /*+  gather_plan_statistics */ e.* FROM   hr.employees e WHERE                                                                                                                                                                                          
e.first_name = 'John' AND      e.last_name = 'Chen'                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Plan hash value: 2077747057                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                                                                                           
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       2 |                                                                                                                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      1 |      1 |      1 |00:00:00.01 |       2 |                                                                                                                                                   
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1 |      1 |      1 |00:00:00.01 |       1 |                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                              
---------------------------------------------------                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                             
   2 - access("E"."LAST_NAME"='Chen' AND "E"."FIRST_NAME"='John')                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                             

 20 rows selected