EXPLAIN PLAN Statement

Display the execution plan for an SQL statement.

Syntax:

   EXPLAIN PLAN [SET STATEMENT_ID = 'text']
      FOR statement;

   EXPLAIN PLAN [SET STATEMENT_ID = 'text']
      INTO [schema.]table@dblink
         FOR statement;

If you omit the INTO TABLE_NAME clause, Oracle will fill a table named PLAN_TABLE

Example

-- Create an empty plan table (adds a table to the current schema)
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

-- Run explain plan
EXPLAIN PLAN FOR
SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

-- Now look at the plan created
SELECT * FROM TABLE(dbms_xplan.display);

-- Delete the records when finished
DELETE from plan_table;
COMMIT;

If the query is fast enough that it can be run to completion in a reasonable amount of time, then just turn on the SQL*Plus AutoTrace feature. Once turned on, this feature will display an execution plan for every subsequent SQL statement you run.

SQL> SET AUTOTRACE ON
SQL> SELECT s.col1, s.col2, h.col3
FROM huge_table h JOIN small_table s USING (demo_id);

Explain plan results

In an explain plan output, the more indented an operation is, the earlier it is executed.
The result of the indented operation is fed to the parent (less indented) operation. In this way you can see the order of execution for the whole statement.

It is possible for several operations to be equally indented and have the same parent. These indentations are calculated from the id, and parent_id columns of the plan_table.

Operations: SELECT, INSERT, UPDATE, DELETE, AND-EQUAL, CONNECT BY, CONCATENATION, COUNT, DOMAIN INDEX, FILTER, FIRST , ROW, FOR UPDATE, HASH JOIN, INDEX, INLIST ITERATOR, INTERSECTION, MERGE JOIN, MINUS, NESTED LOOPS, PARTITION,REMOTE, SEQUENCE, SORT, TABLE ACCESS, UNION, VIEW.

There are also many Options which describe each Operation in more detail - here are a few of the most common:
TABLE ACCESS (FULL) = Full table scan
INDEX (RANGE SCAN) = Read multiple values from an index
INDEX (UNIQUE SCAN) = Read one value from an index
MERGE JOIN () = Sort two tables and merge the sorted rows
SORT (JOIN) = Sort returning multiple rows
SORT (AGGREGATE) = Sort returning one row

"Nobody expects the Spanish Inquisition!!!" - Monty Python

Related:

DBMS_XPLAN - use to format/display the plan table.
ANALYZE
INSERT
SELECT
UPDATE

DBA_SNAPSHOTS        ALL_SNAPSHOTS        USER_SNAPSHOTS
DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
DBA_TABLES           ALL_TABLES           USER_TABLES         TAB
DBA_VIEWS            ALL_VIEWS            USER_VIEWS

DICTIONARY
DICT_COLUMNS
V$EXECUTION


© Copyright SS64.com 1999-2014
Some rights reserved