Skip to content

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