oraclefrontovik

Sharing what I learn

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 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: