Oracle Explain Plan - Performance Tuning
1. Creating a Plan Table
The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema. The following script will create it for you if you don’t already have it:
2. Explain Plan Syntax:
3. Formatting the output
After running EXPLAIN PLAN, Oracle populates the PLAN_TABLE table with data that needs to be formatted to presented to the user in a more readable format. Several scripts exist for this, however, one of the easiest methods available is to cast dbms_xplan.display to a table and select from it (see examples below).
4. Using SQL*Plus Autotrace
SQL*Plus also offers an AUTOTACE facility that will display the query plan and execution statistics as each query executes.
5. FOR only explain information from autotrace, use:
6. For query output and explain information from autotrace, use: