Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to enable AUTOTRACE in Oracle

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
Categories: