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;


    Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

    All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

    I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

    Last modified on
    Tuesday, 14-Jan-2003 00:00:00 EST
    Page Count: 44892