How To Use Oracle AUTOTRACE Utility

With Oracle AUTOTRACE utility users can automatically display SQL execution plans and other  statistics. It shows a report on the execution path used by the SQL optimizer after a successful SELECT, INSERT, UPDATE or DELETE statement. AUTOTRACE is used as a first step towards  SQL performance tuning.
It is different to EXPLAIN PLAN in that it executes the query before the report is generated. EXPLAIN PLAN does not actually execute the query, it only shows how Oracle executes the statement when the statement was explained and user has to query the plan table separately.

Enable AUTOTRACE:
For using AUTOTRACE, users need to have the PLUSTRACE role. This role can be created by SYS user with the sql script which is available at $ORACLE_HOME/sqlplus/admin/plustrce.sql.
Connect as sys user and execute the script. Then Grant PLUSTRACE to required users. It might be good to grant the role to PUBLIC so that everyone can use AUTOTRACE.
The PLAN_TABLE might also need to be created for Oracle version prior to 10g. If that is the case then we can run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create the PLAN_TABLE.

Let us first see what happens when the the role is not available for an user:

test1@ORA10G> set autotrace on;
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Login to the DB server box, connect as Oracle SYS user and create the role:

-bash-3.2$ cd /opt/oracle/product/10.2.0/db_1/sqlplus/admin
-bash-3.2$ ls
glogin.sql  help  iplus  plustrce.sql  pupbld.sql

-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 3 05:50:53 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> @plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;
Role created.

SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$mystat to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

Now grant PLUSTRACE role to all users(public):

SQL> GRANT PLUSTRACE TO PUBLIC;
Grant succeeded.

Using AUTOTRACE:
Once the role is intact for the user, we can enable AUTOTRACE with the command default command "SET AUTOTRACE ON". Users also have different report display options:

SET AUTOTRACE ON : report includes both the execution path and the execution statistics.
SET AUTOTRACE ON EXPLAIN : report shows only the optimizer  execution path, no statistics.
SET AUTOTRACE ON STATISTICS : report shows only the statement execution statistics, no execution path.
SET AUTOTRACE TRACEONLY: report suppresses the query output, only prints execution path and statistics.
SET AUTOTRACE OFF : turns off the AUTOTRACE feature.

Let us see some examples now:

test1@ORA10G> set autotrace on;
test1@ORA10G> select * from inventory where prod_id=1;
   PROD_ID AVAILABLE                     AMOUNT
---------- ------------------------- ----------
         1 Yes                              101
Execution Plan
----------------------------------------------------------
Plan hash value: 947045911
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INVENTORY |     1 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PROD_ID"=1)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        449  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


test1@ORA10G> set autotrace traceonly;
test1@ORA10G> select * from inventory where prod_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 947045911
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INVENTORY |     1 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PROD_ID"=1)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        449  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


test1@ORA10G> set autotrace on explain;
test1@ORA10G> select * from inventory where prod_id=1;
   PROD_ID AVAILABLE                     AMOUNT
---------- ------------------------- ----------
         1 Yes                              101
Execution Plan
----------------------------------------------------------
Plan hash value: 947045911
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INVENTORY |     1 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PROD_ID"=1)
Note
-----
   - dynamic sampling used for this statement


test1@ORA10G> set autotrace traceonly statistics;
test1@ORA10G> select * from inventory where prod_id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        449  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed