Oracle SET AutoTrace ON View execution Plan
Autotrace has the following options
SET AUTOTRACE OFF
No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY
Similar to SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.
SQL > select username, sid, serial#, server, paddr, status from v$session
USERNAME SID SERIAL# SERVER PADDR STATUS
SYS 1631 19 DEDICATED 000000007E8D72D8 ACTIVE
SYS 1634 2 DEDICATED 000000007E8D9A60 INACTIVE
1636 1 DEDICATED 000000007E8D9278 ACTIVE
1639 12 DEDICATED 000000007E8D6AF0 ACTIVE
1641 3 DEDICATED 000000007E8D8A90 ACTIVE
1645 1 DEDICATED 000000007E8D5338 ACTIVE
1646 1 DEDICATED 000000007E8D4B50 ACTIVE
1647 1 DEDICATED 000000007E8D4368 ACTIVE
1648 1 DEDICATED 000000007E8D3B80 ACTIVE
1649 1 DEDICATED 000000007E8D3398 ACTIVE
1650 1 DEDICATED 000000007E8D2BB0 ACTIVE
1651 1 DEDICATED 000000007E8D23C8 ACTIVE
1652 1 DEDICATED 000000007E8D1BE0 ACTIVE
1653 1 DEDICATED 000000007E8D13F8 ACTIVE
1654 1 DEDICATED 000000007E8D0C10 ACTIVE
1655 1 DEDICATED 000000007E8D0428 ACTIVE
16 rows selected.
SQL > set autotrace on statistics
SQL > select username, sid, serial#, server, paddr, status from v$session where username='SYS'
USERNAME SID SERIAL# SERVER PADDR STATUS
SYS 1631 19 DEDICATED 000000007E8D72D8 ACTIVE
SYS 1633 33 DEDICATED 000000007E8D72D8 INACTIVE
SYS 1634 2 DEDICATED 000000007E8D9A60 INACTIVE
Statistics
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
955 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
