Oracle SQL: the Essential Reference

8.1. Using EXPLAIN PLAN

When faced with the task of making a slow SQL statement run faster, you first need to find out how Oracle is currently executing that statement. Whenever you execute a statement, a part of Oracle known as the optimizer constructs a list of steps that will be used to execute the statement. This list of steps is referred to as an execution plan. For example, if you select data from a table to print a report, one possible execution plan is to read all the rows in a table. Most execution plans are a bit more complex than that, and involve such tasks as reading indexes, joining tables, and sorting data.

Oracle includes two optimizers—one rule-based, the other cost-based. The rule-based optimizer is old, and Oracle discourages its use. The cost-based optimizer makes decisions based on statistics collected by the ANALYZE statement. The OPTIMIZER_MODE initialization parameter controls the default optimizer choice for an instance. You can override it at the session level using the ALTER SESSION statement, and at the statement level using hints.

You can discover the execution plan Oracle is going to use for a SQL statement by using Oracle's EXPLAIN PLAN statement. That statement is prepended to the statement you are interested in tuning. For example:

EXPLAIN PLAN
  SET STATEMENT_ID = 'emp_report'
  FOR
  SELECT empno, ename
  FROM emp
  ORDER BY hiredate;

When you use EXPLAIN ...