ORACLE AUTOTRACE

How AUTOTRACE utility works in ORACLE? What are its usage?

·       SET AUTOTRACE ON
·       SET AUTOTRACE OFF
·       SET AUTOTRACE TRACEONLY EXPLAIN
·       SET AUTOTRACE TRACEONLY STAT
·       SET AUTOTRACE ON EXPLAIN
·       SET AUTOTRACE ON STATISTICS

To Read about EXPLAIN PLAN: Click here.

The AUTOTRACE is an easier way to achieve the goal in much better way than Explain Plan. The AUTOTRACE utility use a table called PLAN_TABLE. You must need to define PLAN_TABLE before using AUTOTRACE utility. PLAN_TABLE can be created by running script ORACLE_HOME/rdbms/admin/utlxplan.sql and before starting the AUTOTRACE facility, you must have been granted the PLUSTRACE role. This role is created with the ORACLE_HOME/sqlplus/admin/plustrce.sql supplied script.

The AUTOTRACE works with SET AUTOTRACE command.

SQL> SET AUTOTRACE

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE ON

Before executing Query just run the below command to gather Execution Plan and Stats. Once it is Turned ON, it will display Plan and Stats for all the Queries.

SQL> SET AUTOTRACE ON
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;
ENAME
----------
Ravi

Execution Plan
----------------------------------------------------------
Plan hash value: 888997654
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |    11 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_EMPNO |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP_NO"=1001)

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

Autotrace provides instantaneous feedback including the returned rows, execution plan and statistics. This is important data that can be used to tune your Queries.

In the Autotrace statistics, Compare the “rows processed” with the “consistent gets”. This will give you an idea how many Disk block reads were required to fetch returned rows.

SET AUTOTRACE OFF

To Turn off AUTOTRACE execute below Command.

SQL> SET AUTOTRACE OFF
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;
ENAME
----------
Ravi

SET AUTOTRACE TRACEONLY EXPLAIN

When you do not want to display Statistics then you can Exclude the it by displaying only Execution Plan as below.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;
Execution Plan
----------------------------------------------------------
Plan hash value: 888997654
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |    11 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_EMPNO |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMP_NO"=1001)

SET AUTOTRACE TRACEONLY STAT

When you do not want to display Plan then you can Exclude it by displaying only Statistics as below.

SQL> SET AUTOTRACE TRACEONLY STAT
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;

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

The problem with the above two is that they will not display the Query Output, Only the Execution Plan and Statistics were shown. To do that you can Set the AUTOTRACE as below.

SET AUTOTRACE ON EXPLAIN

This will display the Query Output and Execution Plan of Query.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;
ENAME
----------
Ravi

Execution Plan
----------------------------------------------------------
Plan hash value: 888997654
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    11 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     1 |    11 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_EMPNO |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2    - access("EMP_NO"=1001)

SET AUTOTRACE ON STATISTICS

This will display the Query Output and Statistics of Query.

SQL> SET AUTOTRACE ON STATISTICS
SQL> SELECT ENAME From EMPLOYEES Where EMP_NO=1001;
ENAME
----------
Ravi

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