Autotrace is quite easy to use and provides useful info about the query statistics. It should be the first tool to be used for perf. Testing an application.
Autotrace provides many of the TRACE and TKPROF statistics such as disk reads and total reads.
The main difference between the AUTOTRACE and EXPLAIN PLAN commands in Oracle is that AUTOTRACE actually executes the query (in the way TRACE does) and automatically queries the plan table, whereas EXPLAIN PLAN does neither.
It can be used from SQL*Plus and provides query stats on the screen.
Options for the Autotrace –
• autotrace on – Enables all options.
• autotrace on explain – Displays returned rows and the explain plan.
• autotrace on statistics – Displays returned rows and statistics.
• autotrace trace explain – Displays the execution plan for a select statement without actually executing it. "set autotrace trace explain"
• autotrace traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.
