How do we enable autotrace in oracle?

We can set the autotrace in Oracle and we can see the explain plan for SELECT statement.

This topic is tested in the below oracle version in Windows OS.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

If we don't set the autotrace, then we get the below error.

SQL> connect scott/tiger@dba1
Connected.
SQL> set autotrace on
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>

Here are the steps to resolve the error.

Step 1

Run the plustrace.sql file on sys schema.

In windows, the plustrace file is residing at @$ORACLE_HOME\sqlplus\admin\plustrce.sql
In UNIX, the plustrace file is residing at @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> start d:/oracle/ora92/sqlplus/admin/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>
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_$session to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off
SQL>

Step 2

Grant the plustrace role to Scott schema.

SQL> connect sys/password@dba1 as sysdba
Connected.
SQL> grant plustrace to scott;

Grant succeeded.

SQL>

Step 3

Create the plan_table on sys schema and grant select privileges to Scott schema.

SQL> connect sys/password@dba1 as sysdba
Connected.
SQL> CREATE TABLE PLAN_TABLE (
2 statement_id varchar2(30),
3 plan_id number,
4 timestamp date,
5 remarks varchar2(4000),
6 operation varchar2(30),
7 options varchar2(255),
8 object_node varchar2(128),
9 object_owner varchar2(30),
10 object_name varchar2(30),
11 object_alias varchar2(65),
12 object_instance numeric,
13 object_type varchar2(30),
14 optimizer varchar2(255),
15 search_columns number,
16 id numeric,
17 parent_id numeric,
18 depth numeric,
19 position numeric,
20 cost numeric,
21 cardinality numeric,
22 bytes numeric,
23 other_tag varchar2(255),
24 partition_start varchar2(255),
25 partition_stop varchar2(255),
26 partition_id numeric,
27 other long,
28 distribution varchar2(30),
29 cpu_cost numeric,
30 io_cost numeric,
31 temp_space numeric,
32 access_predicates varchar2(4000),
33 filter_predicates varchar2(4000),
34 projection varchar2(4000),
35 time numeric,
36 qblock_name varchar2(30),
37 other_xml clob
38 );

Table created.

SQL> grant all on plan_table to scott;

Grant succeeded.

SQL>
Step 4

Create the synonym on Scott schema for plan_table which is created in sys schema.

SQL> connect scott/tiger@dba1
Connected.
SQL>
SQL> create synonym plan_table for sys.plan_table;

Synonym created.


Step 5

Connect the scott schema and test the autotrace.

SQL> set autotrace on
SQL> select count(*) from emp;

COUNT(*)
----------
14


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'

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

Here are the different autotrace settings...

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.