Creating EXPLAIN PLAN for Select Queries

1. Create PLAN_TABLE and a synonym so that all user can access this table.

The explain plan process stores data in the PLAN_TABLE. This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows.

-- Creating a shared PLAN_TABLE prior to 11g
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql  
OR SQL> @C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql  -- on windows
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

2. After this AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query. In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.

You can control the report by setting the AUTOTRACE system variable. See Table 11-1.

Table 11-1 Autotrace Settings

Autotrace Setting Result
------------------ -------
SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.

3. But when we try to enable we get following errors:
SQL> SET AUTOTRACE ON
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report


Setups Required for the Autotrace Report
-----------------------------------------
To use this feature, the PLUSTRACE role must be granted to the user, such as HR. DBA privileges are required to grant the PLUSTRACE role.

4. When as a SYS user we tried to assign this role:
SQL> grant plustrace to hr;
grant plustrace to hr
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

5. To fix this, we have to manually create this role by executing below sql:

In Unix: $ORACLE_HOME/sqlplus/admin/plustrce.sql
OR
In Windows: C:\app\ehimkar\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql

6. Now Grant this role to user HR:
SQL> grant plustrace to hr;

Grant succeeded.

7. Now we are able to connect as HR and enable the trace:

SQL> conn hr/hr
Connected.
SQL> set autotrace on;
SQL>

8. Now when we execute any SQL a Explain plan will be generated  quickly for this:

SQL> select * from employees
  2  where employee_id=201;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        201 Michael              Hartstein
MHARTSTE                  515.123.5555         17-FEB-04 MK_MAN          13000
                      100            20



Execution Plan Plan hash value: 1833546154
-------------- --------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU

)| Time     |

--------------------------------------------------------------------------------

-------------

|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0

)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0

)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0

)| 00:00:01 |

--------------------------------------------------------------------------------

-------------


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

   2 - access("EMPLOYEE_ID"=201)


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

SQL>


If you do not want to generate Explain plan like above. If you want only Explain plan to be generated for specific queries then:

Create PLAN_TABLE in HR schema by Executing following script from HR schema:
----------------------------------------------------------------------------

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql  
OR SQL> @C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql  -- on windows

A).

EXPLAIN PLAN SET STATEMENT_ID='t1'
FOR SELECT Employee_id FROM EMPloyees
where employee_id = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
    FROM PLAN_TABLE
    WHERE STATEMENT_ID='t1';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
        19 t1                   SELECT STATEMENT
        19 t1                   INDEX                EMP_EMP_ID_PK

B).
EXPLAIN PLAN SET STATEMENT_ID='em1'
FOR SELECT FIRST_NAME  FROM EMPloyees
where employee_id = 184;

SQL> SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
  2  FROM PLAN_TABLE
  3  WHERE STATEMENT_ID='em1';
  
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         2 em1                  SELECT STATEMENT
         2 em1                  TABLE ACCESS         EMPLOYEES
         2 em1                  INDEX                EMP_EMP_ID_PK

C).
EXPLAIN PLAN SET STATEMENT_ID='e3'
FOR SELECT * FROM EMPloyees
where employee_id = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e3';
  
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         4 e3                   SELECT STATEMENT
         4 e3                   TABLE ACCESS         EMPLOYEES
         4 e3                   INDEX                EMP_EMP_ID_PK
 
D).
EXPLAIN PLAN SET STATEMENT_ID='e4'
FOR SELECT * FROM EMPloyees
WHERE FIRST_NAME = 'Jonathon';

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e4';
   
   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         5 e4                   SELECT STATEMENT
         5 e4                   TABLE ACCESS         EMPLOYEES
 
E). 
EXPLAIN PLAN SET STATEMENT_ID='e5'
FOR SELECT * FROM EMPloyees
WHERE FIRST_NAME = 'Jonathon'
AND EMPLOYEE_ID = 184;

SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e5';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         6 e5                   SELECT STATEMENT
         6 e5                   TABLE ACCESS         EMPLOYEES
         6 e5                   INDEX                EMP_EMP_ID_PK
 
F).  
EXPLAIN PLAN SET STATEMENT_ID='e6'
FOR SELECT * FROM EMPloyees
WHERE EMPLOYEE_ID = 184
AND UPPER(FIRST_NAME) = 'JONATHON';


SELECT PLAN_ID, STATEMENT_ID, OPERATION, OBJECT_NAME
   FROM PLAN_TABLE
   WHERE STATEMENT_ID='e6';

   PLAN_ID STATEMENT_ID         OPERATION            OBJECT_NAME
---------- -------------------- -------------------- --------------------
         6 e5                   SELECT STATEMENT
         6 e5                   TABLE ACCESS         EMPLOYEES
         6 e5                   INDEX                EMP_EMP_ID_PK