Please read warnings at the bottom of this page before using this tip

Oracle 9i has two very interesting features for SQL tuning: the actual execution plan for any given hash value stored in V$SQL_PLAN, and a built-in PL/SQL fucntion named DBMS_XPLAN.DISPLAY to nicely format the data so that you don't have to remember all the CONNECT BY and indentation madness to do it by hand. WHat's missing, though, is a way to display V$SQL_PLAN information using DBMS_XPLAN!

This feature is available starting in 10g, but many sites are still running 9i databases in production. Here's a simple workaround to get you most of the way there:

1. Create a view against V$SQL_PLAN to supply the columns needed by DBMS_XPLAN
2. Supply the name of the new view and the hash value as arguments to DBMS_XPLAN.DISPLAY

Here's the view definition:

create view liveplan as 

select sp.*, 

	to_char(hash_value) statement_id, 	

	sysdate timestamp  

from v$sql_plan sp; 

Now any plan can be retrieved as follows:

SQL> select * from table(dbms_xplan.DISPLAY('LIVEPLAN','1792773701'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------ 
| Id  | Operation                    |  Name             | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       |            |
|   1 |  SORT AGGREGATE              |                   |     1 |       |            |
|   2 |   VIEW                       |                   |     2 |       |            |
|   3 |    UNION-ALL                 |                   |       |       |            |
|   4 |     NESTED LOOPS             |                   |     1 |    78 |     9  (23)|
|   5 |      NESTED LOOPS            |                   |     1 |    52 |     8  (25)|
|   6 |       TABLE ACCESS FULL      | FET$              |     1 |    26 |     7  (29)|
|*  7 |       TABLE ACCESS CLUSTER   | TS$               |     1 |    26 |     2  (50)|
|*  8 |      INDEX UNIQUE SCAN       | I_FILE2           |     1 |    26 |            |
|   9 |     NESTED LOOPS             |                   |     1 |   104 |    18  (39)|
|  10 |      NESTED LOOPS            |                   |     1 |    78 |    17  (42)|
|* 11 |       TABLE ACCESS FULL      | TS$               |     1 |    52 |    11  (55)|
|* 12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1)  |     1 |    26 |            |
|* 13 |      INDEX UNIQUE SCAN       | I_FILE2           |     2 |    52 |            |
---------------------------------------------------------------------------------------

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

   7 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  11 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND
              "TS"."BITMAPPED"<>0)
  12 - filter("TS"."TS#"="F"."KTFBFETSN")
  13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")

29 rows selected.
WARNINGS:

• The query underneath DBMS_XPLAN.DISPLAY is quite expensive (high CPU) when applied to V$SQL_PLAN.

• Do not use this query on systems prior to the 9.2.0.8 patchset, see bug 4434689

• Please use cautiously on production systems.