Autotrace Utility

Autotrace is  beautiful tool provided by Oracle  for getting the explain plan and execution statistics.
You need to know the query and its bind variable if any and with autotrace access, we can get all the useful information about

Autotrace Utility installation
1. cd [ORACLE_HOME]/rdbms/admin2. log into SQL*Plus as SYSTEM3. Run @utlxplan4. Run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;5. Run GRANT ALL ON PLAN_TABLE TO PUBLIC;
6. Log in to SQL*Plus as SYS or as SYSDBA7. Run @plustrce

8. Run GRANT PLUSTRACE TO PUBLIC;

Autotrace 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: This is like SET AUTOTRACE ON, but it suppresses the printing of the user’s query output, if any. This is very useful for queries returning large rows,so we dont need to scroll down that much
SET AUTOTRACE TRACEONLY STATISTICS: This is like SET AUTOTRACE TRACEONLY but it shows the statistics only and supress the explain plan output
SET AUTOTRACE TRACEONLY EXPLAIN: This is like SET AUTOTRACE TRACEONLY but it shows the explain plan only and supress the statistics . This does not execute the select statement ,just parse the statement and shows the explain. INSERT/UPDATE are executed and then explain plan shown

Understanding Autotrace Output

Autotrace shows two things
a) Explain plan: Explain plan shows the plan for query and it shows exactly how the query will be executed in Database.It will shows the cost ,cardinality and bytes for the each step in the explain plan

b) Statistics:  Lots of statistics will be shown.Some of the statistics are
i) Recursive calls: Number of sql statement executed
in order to execute the statement
ii) DB block gets: The no of blocks read from buffer cache in current mode
iii) Consistent gets: The no of blocks read from buffer cache in consistents reads
iv)redo size: redo generated by sql
v) physical reads: No of disks reads