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.
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 ...