Set autotrace in sqlplus

This article describes how AUTOTRACE works in sql*plus.

AUTOTRACE … – changes output results displayed by SQL statement in sql*plus

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

  • EXPLAIN – generate explain plan once SQL statement is finished
  • STATISTICS – generate usage statistics once SQL statement is finished
  • TRACEONLY – fetch all data for executed SQL but don’t display the data – very useful for testing purposes of huge queries
  • combinations of all above options
set autotrace off 
set autotrace on 

set autotrace on explain 
set autotrace on statistics 
set autotrace on explain statistics 

set autotrace traceonly 
set autotrace traceonly explain 
set autotrace traceonly statistics 
set autotrace traceonly explain statistics 

set autotrace off explain 
set autotrace off statistics 
set autotrace off explain statistic

short names can be used

set autot off 
set autot on 

set autot on exp 
set autot on stat 
set autot on exp stat 

set autot trace 
set autot trace exp 
set autot trace stat 
set autot trace exp stat 

set autot off exp 
set autot off stat 
set autot off exp stat

As default autotrace is off in sql*plus

  • set autotrace on
  • set autotrace off

autorace on in addition to sql result generates explain plan and statistics

SQL> set autotrace on
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
            1

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

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

SQL> set autotrace off
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
            1

other combinations. The options limits display for explain and statistics

  • set autotrace on explain
  • set autotrace on statistics
  • set autotrace on explain statistics
SQL> set autotrace on explain
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> set autotrace on statistics
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

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

SQL> set autotrace on explain statistics
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

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

next option is traceonly that blocks display of fetched rows but can display once sql is finished explain plan and statistics

  • set autotrace traceonly
  • set autotrace traceonly explain
  • set autotrace traceonly statistics
  • set autotrace traceonly explain statistics
SQL> set autotrace traceonly
SQL> SELECT 1 output_column FROM DUAL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

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

SQL> set autotrace traceonly explain
SQL> SELECT 1 output_column FROM DUAL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

SQL> set autotrace traceonly statistics
SQL> SELECT 1 output_column FROM DUAL;

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

SQL> set autotrace traceonly explain statistics
SQL> SELECT 1 output_column FROM DUAL;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

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

it seems following options are useless

  • set autotrace off explain
  • set autotrace off statistics
  • set autotrace off explain statistic
SQL> set autotrace off explain
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

SQL> set autotrace off statistics
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

SQL> set autotrace off explain statistic
SQL> SELECT 1 output_column FROM DUAL;

OUTPUT_COLUMN
-------------
 1

Have a fun 🙂
Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *