Improvements to showplan

The output for the set showplan command has been improved as follows:

Indents and delimiters have been added to help readability.
Line numbers and statement numbers help debug long batches and procedures.
Additional messages include the keys used on indexes and messages that provide more information about access methods.
Subquery types, nesting levels, and other subquery information help check subquery performance.
Messages have been added for the new release 11.0 features to display the I/O size and the caching strategy.

In the following example, there is an index named title_ix on the titles table:

select title_id, price
     from titles
     where title = "Computers and Privacy"

This is the pre-11.0 output from showplan:

STEP 1
 The type of query is SELECT.
 FROM TABLE
 titles
 Nested iteration
 Index : title_ix

This is the release 11.0 output from showplan:

QUERY PLAN FOR STATEMENT 1 (at line 1).
 
 
     STEP 1
         The type of query is SELECT.
 
         FROM TABLE
             titles
         Nested iteration.
         Index : title_ix
         Ascending scan.
         Positioning by key.
         Keys are:
             title
         Using I/O Size 2 Kbytes.
         With LRU Buffer Replacement Strategy.

All showplan messages are documented in Chapter 9, “Understanding Query Plans,” of the Performance and Tuning Guide.