How to enable AUTOTRACE in Oracle

By Krystian Zieja on July 1, 2009 - 12:26

Oracle SQL*Plus has a neat feature called AUTOTRACE, which enables us to get execution plan and additional statics about the running SQL statement. The main difference between AUTOTRACE and EXPLAIN PLAN is that AUTOTRACE actually executes the SQL statement. AUTOTRACE can be a helpful tool in Oracle Developer or Oracle DBA hands to check performance of SQL statements.

To enable AUTOTRACE functionality in SQL*Plus we first have to create PLUSTRACE role.

SQL> conn / as sysdba
Connected.
On Windows 

SQL> @%ORACLE_HOME%/sqlplus/admin/plustrce.sql

On *nix systems

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

The output should be like this

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>

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.



SQL>

SQL> set echo off

Then we need to grant PLUSTRACE role to the user, which we want to configure to use AUTOTRACE.

SQL> grant plustrace to user1;



Grant succeeded.

The last thing to do is to connect as a user and create PLAN_TABLE using utlxplan.sql script provided with Oracle Installation

SQL> conn user1/pass1

Connected.

On Windows:

SQL> @%ORACLE_HOME%/rdbms/admin/utlxplan.sql



Table created.

On *nix system:

SQL> @%ORACLE_HOME%/rdbms/admin/utlxplan.sql



Table created.

AUTOTRACE has several settings here is a complete list:

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 Like 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.

Examples

AUTOTRACE ON EXPLAIN

SQL> create table t100 as select * from all_objects

  2  /



Table created.



SQL> set autotrace on explain

SQL> select count(*) from t100 where object_type = 'TABLE'

  2  /



  COUNT(*)

----------

       127





Execution Plan

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

Plan hash value: 1916772590



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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    11 |   229   (1)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| T100 |   304 |  3344 |   229   (1)| 00:00:03 |

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



Predicate Information (identified by operation id):

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



   2 - filter("OBJECT_TYPE"='TABLE')



Note

-----

   - dynamic sampling used for this statement

AUTOTRACE ON STATISTICS

SQL> set autotrace on statistics

SQL> select count(*) from t100 where object_type = 'TABLE'

  2  /



  COUNT(*)

----------

       127





Statistics

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

          0  recursive calls

          0  db block gets

        816  consistent gets

          2  physical reads

          0  redo size

        419  bytes sent via SQL*Net to client

        420  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

AUTOTRACE ON

         
SQL> set autotrace on

SQL> select count(*) from t100 where object_type = 'TABLE'

  2  /



  COUNT(*)

----------

       127





Execution Plan

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

Plan hash value: 1916772590



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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    11 |   229   (1)| 00:00:03 |

|   1 |  SORT AGGREGATE    |       |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| T100 |   304 |  3344 |   229   (1)| 00:00:03 |

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



Predicate Information (identified by operation id):

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



   2 - filter("OBJECT_TYPE"='TABLE')



Note

-----

   - dynamic sampling used for this statement





Statistics

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

          0  recursive calls

          0  db block gets

        816  consistent gets

          0  physical reads

          0  redo size

        419  bytes sent via SQL*Net to client

        420  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

AUTOTRACE TRACE ONLY

SQL> set autotrace traceonly

SQL> select count(*) from t100 where object_type = 'TABLE'

  2  /





Execution Plan

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

Plan hash value: 1916772590



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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    11 |   229   (1)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| T100 |   304 |  3344 |   229   (1)| 00:00:03 |

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



Predicate Information (identified by operation id):

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



   2 - filter("OBJECT_TYPE"='TABLE')



Note

-----

   - dynamic sampling used for this statement





Statistics

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

          0  recursive calls

          0  db block gets

        816  consistent gets

          0  physical reads

          0  redo size

        419  bytes sent via SQL*Net to client

        420  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed