Controlling the Autotrace Report in sql*plus

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Following is the available AUTOTRACE settings.

1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual.

2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics.

3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan.

4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output.

5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execution path as well as execution statistics but does not display query output. If STATISTICS is enabled, query data is still fetched, but not printed.

Thanks.