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.

Related Documents:
-------------------------------------------------------
How to set Environmental variable to SQL*Plus permanently

Sql*plus Basics and Tips

Use of bind variables in Sql*plus

Say yes to Sql*Plus / Sql*Plus Tips

Working with Sql*plus Scripts

What is the difference between $ ! and HOST?

Formatting SQL*Plus Reports

Formatting SQL*Plus Reports Part 2

Change Prompt in SQL*Plus