Oracle Explain Plan

In Oracle Explain Plan a shared PLAN_TABLE is created by default, but you can still create a local version of the table using the “utlxplan.sql” script.

Some operations will likely need the PLUSTRACE role to be granted to the user performing the operation. This role is created using the following script.

SQL> CONN sys/password AS SYSDBA
Connected
SQL> $ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> GRANT plustrace TO my_test_user;

AUTOTRACE

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON
SQL> SELECT *
  2  FROM   emp e, dept d
  3  WHERE  e.deptno = d.deptno
  4  AND    e.ename  = 'SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20         20 RESEARCH       DALLAS



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)




Statistics
----------------------------------------------------------
         81  recursive calls
          4  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

EXPLAIN PLAN

The EXPLAIN PLAN method doesn’t require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL>

Then the execution plan displayed.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |       |      |        |       |       |
|  NESTED LOOPS             |          |       |      |        |       |       |
|   TABLE ACCESS FULL       |EMP       |       |      |        |       |       |
|   TABLE ACCESS BY INDEX RO|DEPT      |       |      |        |       |       |
|    INDEX UNIQUE SCAN      |PK_DEPT   |       |      |        |       |       |
--------------------------------------------------------------------------------

8 rows selected.

SQL>
Statement ID

If multiple people are accessing the same plan table with Oracle Explain Plan, or you would like to keep a history of the execution plans you should use the STATEMENT_ID. This associates a user specified ID with each plan which can be used when retrieving the data.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> @explain.sql TIM

PLAN                                   OBJECT_NAME     OBJECT_TYPE     BYTES  COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement                                                          57     4
  1.1 Nested Loops                                                        57     4
    2.1 Table Access (Full)            EMP             TABLE              37     3
    2.2 Table Access (By Index Rowid)  DEPT            TABLE              20     1
      3.1 Index (Unique Scan)          PK_DEPT         INDEX (UNIQUE)            0

5 rows selected.

SQL>

Also See:

SQL Execution Plan

AWR, ASH Reports