DBA Tips Archive for Oracle

Formatted Explain Plan Output

by Jeff Hunter, Sr. Database Administrator

This article provides several SQL scripts that format information stored in the PLAN_TABLE into a more readable format. With each new release, Oracle has added added many new features.

These scripts are for anyone interested in viewing the access path of SQL queries. DBAs and Developers will use the Oracle supplied script $ORACLE_HOME/rdbms/admin/utlxplan.sql to create the PLAN_TABLE. The plan table is populated with data about the execution plan using the EXPLAIN PLAN statement. Once the plan table is populated, queries are run against the stored data to retrieve the the plan in a readable format.

NOTE: When using these scripts, remember that repeated use of the EXPLAIN PLAN clause does not automatically delete 'old' explain plans from the plan_table, which may result in confusing results. Such confusion may be avoided by deleting 'old' plans manually or using the STATEMENT_ID column to record a plan name which can then be queried.

Creating the 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.

To Populate the Plan Table

SQL> EXPLAIN PLAN FOR
     <your query goes here>;

Explained.

autotrace

The version of SQL*Plus provided with 7.3.4 and above contains a utility called autotrace which allows query plans to be displayed on the fly. To use this utility the current schema must contain a plan table built using the utlxplan.sql mentioned earlier.

To produce a plan without executing the query (in a similar manner to EXPLAIN PLAN FOR) the autotrace string is

SQL> set autotrace traceonly explain

See Automatic EXPLAIN PLANS and TKPROF for more on autotrace.

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 q_plan.lst
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
SQL> spool off

NOTE: The DBMS_XPLAN.DISPLAY() procedure will display the explain plan of the last explain plan command. All examples shown will assume that the PLAN_TABLE table has been created. Also, Also, with SQL*plus, it is recomended to set linesize and pagesize before running these examples. For example: set linesize 100 set pagesize 0

The following can be used to display the explain plan of the last explain plan command. In this example, we do not want to display information related to Parallel Query. We will use the display table function from the DBMS_XPLAN package to display the last explain plan. Force serial option for backward compatibility:

SQL> set markup html preformat on
SQL> SELECT plan_table_output FROM table(dbms_xplan.display('plan_table',null,'serial'));

Use default mode which will display only relevant information. This version of the command will also display Parallel Query information if the plan happens to run parallel:

SQL> set markup html preformat on
SQL> select * from table(dbms_xplan.display());

8.1.5+

In version 8.1.5 and above, scripts were provided to produce a formatted explain plan from the plan table.

These are located in $ORACLE_HOME/rdbms/admin and are called:

utlxplp.sql - UTiLity eXPLain Parallel plans

utlxpls.sql - UTiLity eXPLain Serial plans

Oracle 9i Release 9.2 also adds predicate information to the output.

Alternative 7.3 Explain plan

This example includes the Cost, Cardinality and byte estimates available using explain plan on 7.3, as well as PQO node information:

set echo off

column operation format a16
column options format a15
column object_name  format a20
column id  format 99
column query heading "Query Plan" format a80

SELECT LPAD(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
       ||decode(object_node,'','','['||object_node||'] ')
       ||decode(OPTIMIZER,'','','['||OPTIMIZER||'] ')
       ||decode(id,0,'Cost='||position,
                     decode(COST,'','',' Cost='||COST||' '
                            ||decode(id,0,'','Card='||CARDINALITY||' ')
                            ||decode(id,0,'','Bytes='||BYTES)
                           )
               ) query
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/

set echo on

Version 7

This is an enhanced version of the example select in:

Oracle7 Application Developers Guide (Release 7.0 & 7.1) page B-17

Oracle7 Server Tuning Release 7.2 page A-23

The script below adds query slave details when using the parallel query option PQO), the optimizer_goal of the statement and indicates if optimizer statistics are present for individual objects.

set echo off

column operation format a16
column options format a15
column object_name format a20
column id format 99
column query heading "Query Plan" format a80

SELECT
    LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
    || object_name ||' ' || decode(object_node,'','','[' || object_node || '] ')
    || decode(optimizer,'','','['||optimizer||'] ')
    ||decode(id,0,'Cost = '||position) query
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
 
set echo on

Plan table 'other' column

The other column in the plan_table may contain useful query details depending on the type of query performed. The other column is a long column. The main situations are as follows:

• Referencing remote objects.

  When an object on a remote database is queried, the other column gives details of the query passed to the remote site.

• Using the Parallel Query option (PQO).

  When the PQO is used, the other column gives details of the select that is being performed by the query slave referenced in the query node column.

Example Statement

set echo off
set long 200

column query heading "Query Plan" format a45
column other heading 'Other' format a34

SELECT
    LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
    || object_name || ' '
    || decode(object_node,'','','[' || object_node || '] ') query
  , other
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id;

set echo on

Version 6

From ORACLE RDBMS Performance Tuning Guide Version 6.0 page 7-15:

SELECT lpad(' ',2*level)||operation||' '||options||' '||object_name "Query Plan"
FROM   plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 1;