Understanding Oracle Explain Plans

About the plan table

The PLAN_TABLE (see example below) is used to visualize the execution plan. This table is used by most tools that display an explain plan.

Example Oracle Plan Table

This table is created by using the script: <Oracle Home>
dmbsadminutlxplan.sql.

NOTE

Oracle10g+ auto generates this table now. No need to run the script after Oracle10g.  IF any of the tools complain about this table not being present…have your DBA create a public synonym for it under the SYS account.  Be sure to drop the PLAN_TABLE in your schema.

NOTE

It is important to rerun this script when migrating to a newer instance of Oracle. There are always newer columns and you will not be taking advantage of the latest features of Oracle without using the latest PLAN_TABLE. In Oracle11g+, this table is automatically generated during the migration process.

Viewing explain plans with SQL*Plus

SQL*Plus, a basic Oracle utility, can display explain plans and SQL runtime statistics. SQL*Plus can also easily show execution times using ‘SET TIMING ON’ syntax.

This article illustrates a ‘show_plan.sql’ script useful to display the exact relationships of the related explain plan information.

There is an undocumented explain plan from Oracle Corp as well, that displays some very useful information.

SQL*Plus always works. It is available in a command-line version and a windows interface (except for Oracle11g). The windows version of SQL*Plus seems to be depreciated in the Oracle11g Client-side software installation.

Working with explain plans using SQL*Plus

SQL*Plus supports autotrace. The syntax ‘set autotrace’ will show all the options available.

Example: Using SQL*Plus to run autotrace 

TIP:
Set linesize 100

The example below shows the output of the following command:

set autotrace on explain statistics

Example Set Autotrace

Notice the newer style explain plan. This explain plan displays the cost numbers, the execution plan, the statement id’s. The’*’ on some statement ids indicates there is a relationship between this explain plan line and one or more where clause predicates from the original SQL statement. Displaying this where-clause code as it relates to the explain plan makes understanding how Oracle arrived at an explain plan easier.

These are the runtime SQL statistics associated with this SQL statement.

• Get <file specs>
• Save <file specs>
• Start <file specs>
• EDIT
  • Puts SQL into notepad
  • Save and exit
• /
  • Runs current SQL

The important SQL*Plus command to remember is ‘EDIT’ and ‘/’. Edit opens the SQL into a notepad session and ‘/’ executes the SQL. ‘GET’ opens a file containing SQL code from a folder. ‘START’ opens a file containing SQL code and executes it.

Example Explain Plan For

Getting an explain plan without first running the SQL

Sometimes it is preferable to get an explain plan without running the SQL code first. You already know it runs slowly. The syntax ‘EXPLAIN PLAN FOR’ in front of the SQL code will cause the PLAN_TABLE to be populated with an explain plan. Notice the ‘Explained’ comment from SQL*Plus. When running with the EXPLAIN PLAN FOR syntax. SQL*Plus responds with ‘Explained. Now the PLAN_TABLE is populated. Use the ‘start show_plan.sql’ script or the ‘DBMS_XPLAN.DISPLAY’ to format the explain plan.

SHOW_PLAN.SQL Script

This is the code of  the ‘show_plan.sql’ script.

Format the plan table

Use the syntax ‘SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)’ to format the contents of the PLAN_TABLE.

Formatting the PLAN_TABLE

There are a number of DBMS_XPLAN options available. Oracle10g+ has documented these options.

Example DBMS_XPLAN

NOTE

The newer DBMS_XPLAN.DISPLAY does not show the statement ID and parent ID numbers. These numbers are sometimes important to help figure out the order that the explain plan was processed in. This order of execution matters so when working with larger explain plans, you might try using the ‘show_plan.sql’ script to show the relationship numbers. JS Tuner also contains a version of this script, showing this same relationship.

NOTE

The SHOW_PLAN.sql script, as well as other scripts listed in this book are available for download section of my website: www.DanHotka.com.

New plan table columns

Notice as the database progresses, additional columns of useful information also appear in the PLAN_TABLE.

• Oracle 9.0: IO_Cost, CPU_Cost, Temp_space
• Oracle 9.2: Access_Predicates, Filter_Predicates
• Oracle 10g: plan_id, object_alias, depth, Projection (goes with Access and Filte Predicates), time (the CBO-estimated time for each line of the explain plan), qblock_name (subquery processing),
• Oracle 11g: Other_xml (dob)

Use the ‘qblock_name’ hint to label the subqueries in the explain plan!  TIME might also be a useful column in the future.

The Access_Predicates and Filter_Predicates are now populated with the where clause items from the SQL. These columns appear in all of the tools when using the cost-based optimizer.

?