Oracle AUTOTRACE.

When you run sql query, it's important to get execution path used by SQL optimizer and execution statistics. In SQL Plus there is an option to get it automatically. The report will be generated after a successful SQL DML statement, such as INSERT, SELECT or DELETE. It's useful for monitoring and for tuning the performance of the queries.
To allow this functionality you should do the following steps:
1. Create Plan Table:
CONNECT / AS SYSDBA
@$ORACLE_HOME/rdbms/admin/catplan.sql
2. Create PLUSTRACE Role
CONNECT / AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
3. Grant PLUSTRACE Role to relevant user(s)
CONNECT / AS SYSDBA
GRANT PLUSTRACE TO HR;
Grant succeeded.
Usage:
Syntax SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Examples.
1. Trace and Run
SQL> set autotrace on
SQL> SELECT * FROM dual; 
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
2. Trace Only
SQL> set autotrace traceonly
SQL> SELECT * FROM dual; 
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
3. Trace and Explain
SQL> set autotrace traceonly explain
SQL> SELECT * FROM dual; 
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL>
4. Trace and Statistics
SQL> set autotrace traceonly statistics
SQL> SELECT * FROM dual; 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
5. Stop Tracing SET AUTOTRACE OFF
SQL> set autotrace off
SQL> /
D
-
X
SQL>