EXPLAIN PLAN parses a query and records the “plan” that Oracle devises to execute it. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan. We will focus on using it through SQLPlus since most Oracle programmers have access to SQLPlus.
Contents [hide]
1 Creating a Plan Table
2 Explain Plan Syntax
3 Formatting the output
4 Some Examples
5 Using SQL*Plus Autotrace
6 Also see
[edit] 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 have it already:
@?/rdbms/admin/utlxplan.sql
[edit] Explain Plan Syntax
EXPLAIN PLAN FOR your-sql-statement;
or
EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR your-sql-statement;
[edit] 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).
[edit] Some Examples
SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;
Explained.
SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2852011669
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 – access(“DEPTNO”=40)
14 rows selected.
[edit] Using SQL*Plus Autotrace
SQL*Plus also offers an AUTOTRACE facility that will display the query plan and execution statistics as each query executes. Example:
SQL> SET AUTOTRACE ON
SQL> select * from dept where deptno = 40;
DEPTNO DNAME LOC 40 OPERATIONS BOSTONExecution Plan
Plan hash value: 2852011669
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 – access(“DEPTNO”=40)
Statistics
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
443 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed