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
Very useful.
Why we autotrace for our sql i mean how this should be enable for application user?