Explain Plans

EXPLAIN PLAN is an Oracle SQL command which will parse the relevant SQL, and store the query plan derived by the optimizer in an appropriate table (normally PLAN_TABLE).

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp
  4  WHERE  mgr = 7902
  5  /

Explained.

The statement can be given a specific ’statement id’ to make querying the plan table simpler.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_QUERY'
  2  FOR
  3  SELECT *
  4  FROM   emp
  5  WHERE  mgr = 7902
  6  /

Explained.

The PLAN_TABLE can then be queried to show the query plan chosen by the optimizer. This isn’t necessarily the same plan which has been or will be used by the optimizer to execute the same query. Where there is a difference it is normally because of one of the following reasons :

  • EXPLAIN PLAN does not make use of bind variable peeking which can affect which plan is chosen by the optimizer.
  • Statistics were gathered, but existing plans were not invalidated to take account of those new statistics
  • The session status for the EXPLAIN PLAN did not have all the same settings (eg optimizer goal, sort area size etc) as that executing the query.
  • Different bind variable data types. If the variable used in the original query is a date or a number, the appropriate TO_DATE or TO_NUMBER should be included in the EXPLAIN PLAN statement, otherwise a character data type will be assumed.

Under 9i and later, the best way to query the PLAN_TABLE is though DBMS_XPLAN :

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     2 |    74 |     2 |
|*  1 |  TABLE ACCESS FULL   | EMP         |     2 |    74 |     2 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
   1 - filter("EMP"."MGR"=7902)

Note: cpu costing is off

14 rows selected.

If you specified a ‘statement_id’ during explain plan, you should specify it as a parameter when viewing the plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MY_QUERY','ALL'))
  2  /

Either of these will report an ‘old’ message if the plan table does not contain all of the columns which are appropriate for that version of Oracle. Under 9i and later, the plan table should contain the columns :

CPU_COST            NUMBER(38)
IO_COST             NUMBER(38)
TEMP_SPACE          NUMBER(38)
ACCESS_PREDICATES   VARCHAR2(4000)
FILTER_PREDICATES   VARCHAR2(4000)

10GR2 tables should also have :

PROJECTION          VARCHAR2(4000)
TIME                NUMBER(38,10)
QBLOCK_NAME         VARCHAR2(30)