Oracle's Autotrace Use

Sqlplus's autotrace is a very simple and convenient tool for analyzing the execution plan of SQL, and is a very useful tool in most cases. The SQL execution plan and execution status provided by the Autotrace tool provide the basis for optimization when we optimize SQL, as well as the obvious contrast effect of the optimization effect.
Usage: SET autot[race] {OFF | On | Trace[only]} [Exp[lain]] [stat[istics]

Example:
SET Autot[race] off stop Autotrace
SET Autot[race] on autotrace, displaying autotrace information and SQL execution results
SET Autot[race] traceonly turn on Autotrace, show only autotrace information
SET Autot[race] on EXPLAIN open autotrace, only autotrace EXPLAIN information is displayed
SET Autot[race] On statistics open autotrace, only autotrace statistics information is displayed

Results explained
Physical reads physical read-the number of blocks of data read from the hard disk during SQL execution
Redo size Redo Number-The amount of redo log generated during SQL execution
Bytes set via sql*net to client bytes sent via sql*net
Bytes received via sql*net the number of bytes accepted by the client via Sql*net
Sorts (memory) the sort that takes place in RAM
Sorts (disk) cannot be sorted in memory, requires hard drive to assist
Rows processed number of records for results

Autotrace Considerations for Optimization

1. You can set timing to get the time it takes to execute SQL, but you can't just use this time as the only measure of SQL execution efficiency. This time will include some time spent on autotrace, so this time is not just the time for SQL execution. This time has a certain error with the SQL execution time, which is especially noticeable when SQL is relatively simple.

2. Determine the amount of SQL efficiency that should be read by executing the logical reads in the SQL execution state
Logical Read = (db block gets+ consistent gets)
Summarize

Autotrace is the most basic tool in Oracle's optimization tools, and although it is limited in functionality, it is sufficient to meet our daily needs.

In oracle9i you need to run the $oracle_home/rdbms/admin/utlxplan.sql script to generate the Plan_table table;
Plan_table no longer needs to be created in oracle10g, Oracle adds a dictionary table plan_table$ by default, and then creates a common synonym for users based on plan_table$

Notes on several common options for autotrace:
SET AUTOTRACE OFF----------------does not generate a AUTOTRACE report, which is the default mode
SET AUTOTRACE on EXPLAIN------AUTOTRACE only show optimizer execution path report
SET AUTOTRACE on STATISTICS--Displays only execution statistics
SET AUTOTRACE on-----------------contains execution plans and statistics
Set AUTOTRACE traceonly------Same set AUTOTRACE on, but does not display query output

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/seven_tt/archive/2008/11/29/3409141.aspx

Oracle's Autotrace Use