All About Explain Plan

Introduction

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

Using V$SQL_PLAN

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement:

Using Explain Plan

Steps

• EXPLAIN PLAN FOR   SELECT last_name FROM employees;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

About Autotrace

1. cd $oracle_home/rdbms/admin

2. log into sqlplus as system

3. run SQL> @utlxplan

4. run SQL> create public synonym plan_table for plan_table

5. run SQL> grant all on plan_table to public

6. exit sqlplus and cd $oracle_home/sqlplus/admin

7. log into sqlplus as SYS

8. run SQL> @plustrce

9. run SQL> grant plustrace to public

 

You can control the report by setting the AUTOTRACE system variable.

• SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path. 
• SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.  
• SET AUTOTRACE ON  - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.  
• SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.