Oracle provides the EXPLAIN PLAN SQL statement as a way to query the optimizer. By preceding the SQL statement you want to examine with the EXPLAIN PLAN statement, you direct Oracle to populate the PLAN_TABLE table with information about the access plan for that SQL statement. The EXPLAIN PLAN statement contains options for specifying a statement identifier so that multiple statements can be stored in the PLAN_TABLE. You can also provide an override for the PLAN_TABLE by specifying any other table by name that has the same structure. For details of the EXPLAIN PLAN statement, see Chapter 11.

The following example shows how to use EXPLAIN PLAN and retrieve information from PLAN_TABLE. Note that this is the same structure used by AUTOTRACE, TKPROF, and the Oracle Enterprise Manager (OEM) Top Sessions display, as we’ll describe in later sections.

SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'Sample Query'
  3  FOR
  4  SELECT emp.ename,emp.empno,emp.job,dept.dname
  5  FROM emp,dept
  6  WHERE emp.deptno = dept.deptno
  7  /

Explained.

SQL> SELECT LPAD('  ',2*(LEVEL-1))||OPERATION Operation,
  2          OPTIONS Options,
  3          OBJECT_NAME "Object Name",
  4          POSITION Position
  5  FROM PLAN_TABLE
  6  START WITH ID = 0 and STATEMENT_ID = 'Sample Query'
  7  CONNECT BY PRIOR id = parent_id
  8       and STATEMENT_ID = 'Sample Query';

OPERATION            OPTIONS    Object Nam  POSITION 
-------------------- ---------- ---------- ---------  
SELECT STATEMENT  
  NESTED LOOPS   1   
    TABLE ACCESS     FULL       EMP                1   
    TABLE ACCESS     BY ROWID   DEPT               2  
      INDEX          RANGE SCAN DEPTI              1  

5 rows selected.