Obtaining a Formatted Explain Plan

Print this post

1. Create a Plan Table
----------------------------

Use the utlxplan.sql script to create the table as instructed below.

SQL> @?/rdbms/admin/utlxplan

Note that the plan table format can change between versions so ensure that you create it using the utlxplan script from the current version.

2. To Populate the Plan Table
--------------------------------------

SQL> explain plan for

Explained.

3. Displaying The Execution Plan
------------------------------------------

Version 9.2
--------------

With Oracle 9i version 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.

To generate a formatted explain plan of the query that has just been 'explained':

SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from
table(dbms_xplan.display(‘PLAN_TABLE’, Null,’ALL’));
SQL> spool off

Version 10.2
----------------

In addition to the standard explain plan option, you can pull execution plans from the library cache if the SQL has already been executed.

a) To get the plan of the last executed SQL issue the following:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from table(dbms_xplan.display_cursor(null,null,
'ALL'));

b) If you know the hash value of the SQL, you can use
dbms_xplan.display_cursor as follows:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD));

c) We can also get run time statistics with some additional
options and if we use the gather_plan_statistics hint.

e.g:

SQL> select /*+ gather_plan_statistics */ col1, col2 etc.....
SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',
&CHILD,'ALL IOSTATS LAST'))

sql_id: specifies the sql_id value for a specific SQL statement, as shown in V$SQL.SQL_ID, V$SESSION.SQL_ID or V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last executed statement of the current session is shown.

cursor_child_no: specifies the child number for a specific sql cursor, as in V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER. If not specified, all child cursors for the specified sql_id are displayed.

Note that to use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate error message.