EXPLAIN PLAN is a SQL statement that causes Oracle to report the execution plan it would choose for any SELECT, INSERT, UPDATE, or DELETE statement. An execution plan refers to the approach Oracle will take to retrieve the necessary data for a statement. One example of a plan would be to use an index to find the required rows. Another example of an execution plan would be to sequentially read all rows in the table. If you have a poorly-performing SQL statement, you can use EXPLAIN PLAN to find out how Oracle is processing it. With that information, you may be able to take some corrective action to improve performance.
When you use EXPLAIN PLAN, Oracle doesn’t display its execution strategy on the screen; instead, it inserts rows into a table. This table is referred to as the plan table , and you must query it properly in order to see the results. Of course, the plan table must exist, so if you’ve never used EXPLAIN PLAN before, you may need to create the plan table first.
Tip
Oracle occasionally adds columns to the plan table. If you have a plan table created using a previous version of Oracle, you may want to drop and recreate it, just to be sure you have the most up-to-date version.
Oracle provides a script to create the
plan table. It is named
UTLXPLAN.SQL
, and it resides in the
RDBMS/ADMIN directory for your database. Under
Windows 95, for example, the script to create the plan table for
Oracle8 will be
C:ORAWIN95RDBMS80ADMINUTLXPLAN.SQL. You can
run it from SQL*Plus like this:
SQL> @C:ORAWIN95RDBMS80ADMINUTLXPLAN.SQL
Table created.Here is what the Oracle8 plan table looks like:
SQL> DESCRIBE plan_table
Name Null? Type
------------------------------- -------- ----
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONGThe name of the table does not have to be plan_table, but
that’s the default, and it’s usually easiest to leave it
that way. If for some reason you don’t have access to the
UTLXPLAN.SQL script, you can create the table
manually. Just be sure that the column names and datatypes match
those shown here.
The columns in the plan table may vary a bit depending on the exact Oracle version you have. The table shown above is for Oracle 8.0.3, and includes at least three columns that are new with Oracle8. The PARTITION_START, PARTITION_STOP, and PARTITION_ID columns were added in support of Oracle8’s new partitioning features.
Once you have a plan table, getting Oracle to tell you the execution plan for any given query is a fairly easy task. You just need to prepend the EXPLAIN PLAN command to the front of your query. The syntax for EXPLAIN PLAN looks like this:
EXPLAIN PLAN
[SET STATEMENT_ID = `
statement_id']
[INTO table_name]
FOR statement;where:
- statement_id
Can be anything you like, and is stored in the STATEMENT_ID field of all plan table records related to the query you are explaining. It defaults to null.
- table_name
Is the name of the plan table, and defaults to “PLAN_TABLE”. You only need to supply this value if you have created your plan table with some name other than the default.
- statement
Is the DML statement to be “explained.” This can be an INSERT, UPDATE, DELETE, or SELECT statement, but it must not reference any data dictionary views or dynamic performance tables.
Consider the following query, which returns the total number of hours worked by each employee on each project:
SELECT employee_name, project_name, sum(hours_logged) FROM employee, project, project_hours WHERE employee.employee_id = project_hours.employee_id AND project.project_id = project_hours.project_id GROUP BY employee_name, project_name;
This query can be explained using the following two commands:
DELETE FROM plan_table WHERE statement_id = 'HOURS_BY_PROJECT'; EXPLAIN PLAN SET STATEMENT_ID = 'HOURS_BY_PROJECT' FOR SELECT employee_name, project_name, sum(hours_logged) FROM employee, project, project_hours WHERE employee.employee_id = project_hours.employee_id AND project.project_id = project_hours.project_id GROUP BY employee_name, project_name;
When you execute this EXPLAIN PLAN command, you won’t see any output. That’s because Oracle stores the query plan in the plan table. Retrieving and interpreting the results is your next task.
Note
You must include a DELETE statement prior to the EXPLAIN PLAN statement. When you explain a statement, Oracle does not clear the plan table of any previous rows with the same statement ID. If rows with the same statement ID exist from previous executions of EXPLAIN PLAN, you will get very strange results.
If you’re the only person using the plan table, you can save yourself some typing by omitting the WHERE clause in the DELETE statement, thereby deleting all the records in the plan table.
Having done an EXPLAIN PLAN, you retrieve and view the results by querying the plan table. The statement ID is key to doing this. The plan table can contain execution plans for any number of queries. The rows for each query contain the statement ID you specified in your EXPLAIN PLAN statement, so you must use this same ID when querying the plan table in order to select the plan you are interested in seeing.
The standard way to look at an execution plan is to display it using a hierarchical query. Oracle breaks query execution down into a series of nested steps, each of which feeds data up to a parent step. The ultimate parent is the query itself, the output of which is returned to the application. Here is a typical query used to display the plan output:
SELECT id, parent_id,
LPAD(' ', 2*(level-1)) || operation || ' ' || options
|| ' ' || object_name || ' ' ||
DECODE(id, 0, 'Cost = ' || position) "Query Plan"
FROM plan_table
START WITH id = 0 AND statement_id = '&&s_statement_id'
CONNECT BY prior id = parent_id AND statement_id = '&&s_statement_id';The result of this query will be a report showing the steps in the execution plan, with each child step being indented underneath its parent.
You don’t want to type a plan table query each time you need to see a plan, so you should consider placing it in a script file. The following script provides a user-friendly way to see the execution plan for a statement. It first lists the statements currently available in the plan table. Then you are prompted for the one you want to look at, and finally the plan for that statement is displayed. Here is the script:
SET ECHO OFF
--DESCRIPTION
--This script, SHOW_PLAN.SQL, displays a list of statement IDs from
--the plan table, and prompts the user to enter one. The plan for
--that statement is then displayed using a hierarchical query.
--
--MODIFICATION HISTORY
--19-Aug-1998 by Jonathan Gennick
-- 1 Creation
--
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
--
--Display a list of statement ids for the user to choose from.
--
PROMPT
PROMPT The plan table contains execution plans
PROMPT for the following statements:
PROMPT
SELECT DISTINCT ' ', statement_id
FROM plan_table
ORDER BY statement_id;
--
--Ask the user to enter the name of the statement for
--which the execution plan is to be shown.
--
PROMPT
ACCEPT s_statement_id CHAR PROMPT 'Enter Statement ID: '
PROMPT
--
--Show the execution plan for the statement the user selected.
--
COLUMN id FORMAT 999
COLUMN step_description FORMAT A80
SELECT id, LPAD(' ', 2*(level-1)) || operation || ' ' || options
|| ' ' || object_name || ' ' ||
DECODE(id, 0, 'Cost = ' || position) step_description
FROM plan_table
START WITH id = 0 AND statement_id = '&&s_statement_id'
CONNECT BY prior id = parent_id AND statement_id = '&&s_statement_id'
ORDER BY id, position;
SELECT 'PLAN_TABLE contains no execution plan for &&s_statement_id..'
FROM dual
WHERE '&&s_statement_id' NOT IN (
SELECT DISTINCT statement_id
FROM plan_table
);
--Restore settings to their defaults
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 14You can execute the SHOW_PLAN script and display the plan for the HOURS_BY_PROJECT query explained earlier as follows:
SQL>@show_planThe plan table contains execution plans for the following statements: HOURS_BY_PROJECT Enter Statement ID:HOURS_BY_PROJECT0 SELECT STATEMENT Cost = 21 1 SORT GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL EMPLOYEE 4 HASH JOIN 5 TABLE ACCESS FULL PROJECT 6 TABLE ACCESS FULL PROJECT_HOURS SQL>
Each element of this execution plan contains three pieces of information: the operation, any options that apply, and the object of that operation. Usually these three elements are enough to figure out what Oracle is doing with the query, but if you need more information about a specific step, you can always query the plan table.
Tip
In order for Oracle to compute a reasonably accurate cost, you must have up-to-date statistics on the tables involved in the query. Use SQL’s ANALYZE TABLE command to gather these statistics. If your statistics are old, the optimizer may come up with an execution plan that won’t be efficient for the data you have now.
The SHOW_PLAN script also returns the overall cost of executing the query. In this example, the cost is 21. This number has no meaning in an absolute sense. It’s simply a scoring mechanism used by the optimizer to facilitate choosing one plan from many possibilities. You should use it only when comparing two execution plans to see which is more efficient. A plan with a cost of 21, for example, would be approximately twice as efficient as a plan with a cost of 42.
The key to interpreting an execution plan is to understand that the display is hierarchical. A step may consist of one or more child steps, and these child steps are shown indented underneath their parent. Executing any given step involves executing all its children, so to understand the plan, you pretty much have to work your way out from the innermost step. In this example, there are three major steps to the plan. First, Oracle will join the PROJECT and PROJECT_HOURS tables, using a hash join method. Next, the results of this join will be joined with the EMPLOYEE table, also using a hash join method. Finally, the results are sorted on the GROUP BY columns. After the sort, the rows are returned as the result of the SELECT statement.
Table 8.1 gives a brief description of the various operations, together with their options, that you may see when querying the plan table. For more detailed information about any of these operations, refer to the Oracle8 Server Tuning manual.
Table 8-1. EXPLAIN PLAN Operations