SQL Execution Plans in SQL Plus

source: http://www.dba-oracle.com/plsql/t_plsql_plans.htm

- Execution plan, statement must run to completion
- Create "plan_table" table, synonym using "utlxplan.sql" script and grant access for public

 
$ sqlplus / as sysdba

SQL> @?/rdbms/admin/utlxplan.sql

SQL> grant all on sys.plan_table to public;

- Create public synonym for plan table

 
SQL> create public synonym plan_table for sys.plan_table;

- Execution plans using the autotrace feature of SQL*Plus

 
SQL> set autotrace on;
SQL> set linesize 2000
SQL> select * from scott.emp;
     EMPNO|ENAME     |JOB      |       MGR|HIREDATE |       SAL|      COMM|    DEPTNO
----------|----------|---------|----------|---------|----------|----------|----------
      7369|SMITH     |CLERK    |      7902|17-DEC-80|       800|          |        20
      7499|ALLEN     |SALESMAN |      7698|20-FEB-81|      1600|       300|        30
      7521|WARD      |SALESMAN |      7698|22-FEB-81|      1250|       500|        30
      7566|JONES     |MANAGER  |      7839|02-APR-81|      2975|          |        20
      7654|MARTIN    |SALESMAN |      7698|28-SEP-81|      1250|      1400|        30
      7698|BLAKE     |MANAGER  |      7839|01-MAY-81|      2850|          |        30
      7782|CLARK     |MANAGER  |      7839|09-JUN-81|      2450|          |        10
      7788|SCOTT     |ANALYST  |      7566|19-APR-87|      3000|          |        20
      7839|KING      |PRESIDENT|          |17-NOV-81|      5000|          |        10
      7844|TURNER    |SALESMAN |      7698|08-SEP-81|      1500|         0|        30
      7876|ADAMS     |CLERK    |      7788|23-MAY-87|      1100|          |        20
      7900|JAMES     |CLERK    |      7698|03-DEC-81|       950|          |        30
      7902|FORD      |ANALYST  |      7566|03-DEC-81|      3000|          |        20
      7934|MILLER    |CLERK    |      7782|23-JAN-82|      1300|          |        10

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
         59  consistent gets
          6  physical reads
          0  redo size
       1454  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         14  rows processed

- Explain plan, statement generates the execution plan for a query without executing the query itself

 
SQL> conn scott
Enter password: 
Connected.

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> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

- For mulitple DBA's using you can set statement_id='dba1'

 
SQL> conn scott
Enter password: 
Connected.

SQL> explain plan set statement_id = 'dba1' for
  2  select * 
  3  from emp e, dept d
  4  where e.deptno=d.deptno
  5  and e.ename='SMITH';

Explained.

SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    58 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    58 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.