Using DBMS_XPLAN.DISPLAY on V$SQL_PLAN Print

Using DBMS_XPLAN.DISPLAY on V$SQL_PLAN Print
A method to take advantage of DBMS_XPLAN in conjunction with V$SQL_PLAN on Oracle 9i.

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.