Oracle SQL*Plus: The Definitive Guide

Using AUTOTRACE

Beginning with version 3.3 of SQL*Plus, Oracle provides a setting that automatically displays the execution plan for any query you execute. The name of this setting is AUTOTRACE, and you can turn it off and on with the SET command. There is one big catch. The query must actually be executed before you can see the results. If you are contemplating a query against a large table, it might take all day for a poorly-tuned query to execute. In that case, you might just want to see the execution plan before you run the query, not afterwards. You may also not want this behavior if you are writing a DELETE or an UPDATE statement, because you would need to actually delete or update some data in order to see the execution plan.

Tip

Before you can use AUTOTRACE to display execution plans, you must have created a plan table. AUTOTRACE uses this table, and expects the name to be PLAN_TABLE, which is the default name if you use the UTLXPLAN.SQL script to create it.

Granting Access to the Performance Views

AUTOTRACE will do more than just display the execution plan for a query. It also displays statistics that show you how much disk I/O and network traffic occurred during a query’s execution. Other information, such as the number of sorts performed on the data, is given as well.

In order to see the statistical data AUTOTRACE returns, you must have SELECT access to certain of Oracle’s dynamic performance views . Dynamic performance views, whose names usually begin with V$ or V_$, ...