OracleSource

In SQL*Plus, you can automatically obtain the execution plan and some additional statistics on the running of a SQL command by using the AUTOTRACE setting. Unlike the EXPLAIN PLAN command, the statement is actually run, even if you choose to suppress the statement output; thus you can display realistic statistics. AUTOTRACE, a feature available since Oracle Server release 7.3, is an excellent diagnostic tool for SQL Statement Tuning.

Because it is purely declarative, it is easier to use than EXPLAIN PLAN.

Command Options
OFF Disables autotracing SQL statements
ON Enables autotracing SQL statements
TRACEONLY Enables autotracing SQL statements and suppresses statement output
EXPLAIN Displays execution plans but does not display statistics
STATISTICS Displays statistics but does not display execution plans

Note: If both command options EXPLAIN and STATISTICS are omitted, execution plans and statistics are displayed by default.

SQL*PLUS AUTOTRACE Examples

To start tracing statements using AUTOTRACE:
SQL> set autotrace on

To hide statement output:
SQL> set autotrace traceonly

To display execution plans only:
SQL> set autotrace traceonly explain

Prerequisites for Using AUTOTRACE
To use the EXPLAIN option of AUTOTRACE, you must first create the table PLAN_TABLE in your schema, just as you would need to do before using the EXPLAIN PLAN command.
To access STATISTICS data, you must have access to several dynamic performance tables. The DBA can grant access by using the role plustrace created in the plustrce.sql script. (This script name may vary by operating system.)

The DBA must run the script as the SYS user and grant the role to users who want to use the STATISTICS option of AUTOTRACE.
Or you can try connecting as SYSDBA : -
SQL> conn / as sysdba

SQL*PLUS AUTOTRACE Statistics

SQL> set autotrace traceonly statistics
SQL> select * from dual;
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


AUTOTRACE Statistics
AUTOTRACE displays several statistics. The most important ones are the following:
db block gets The number of logical I/Os for current gets consistent gets The number of logical I/Os for read-consistent gets
physical reads The number of blocks read from disk
redo size The amount of redo generated (for DML statements)
sorts (memory) The number of sorts performed in memory
sorts (disk) The number of sorts performed using temporary disk storage

Note that current gets are also known as dirty reads. Note that consistent gets can be higher than expected due to array processing; each new array needs one additional get. The number of array fetches equals (SQL*Net roundtrips - 2).
For some reason, each SQL statement shows one sort in memory.