How to Use an Explain Plan Table in Oracle

Whenever an SQL query is issued to the Oracle database, Oracle creates a plan for executing the query. This execution plan can be saved in a plan table. The plan table contains the order of the tables referenced by the statement, the method used to access the table, the join method, and data operations. Plan tables can be used to determine how the plan is executed so that you can optimize the operations.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Oracle Database

1. Start SQL*Plus by typing "sqlplus" at a command prompt.

2. Create a table to hold the EXPLAIN PLAN output with the following syntax:
   CONNECT EXAMPLE/password @$ORACLE_HOME/RDMS/ADMIN/UTLXPLAN.SQL

3. Run the EXPLAIN PLAN by placing "EXPLAIN PLAN FOR" in front of the SQL statement. Use the "SET STATEMENT_ID" clause if you have more than one statement in the plan table. For example, "EXPLAIN PLAN SET STATEMENT_ID = 'st1' FOR SELECT zip FROM address;"

4. Type the command "@$ORACLE_HOME/rdbms/admin/utlxpls.sql" to view the Explain Plan.

5. Type "quit" to close the SQL*Plus session.

References

• Using EXPLAIN PLAN
• Oracle's Explain Plan
• SQL Execution Plans

Resources

• Oracle Database