Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Using AUTOTRACE

Oracle SQL*Plus provides an AUTOTRACE setting that automatically displays the execution plan for any query you execute. You can turn AUTOTRACE off and on with the SET command. There is one big catch: the query must be executed before you can see the results. The problem with this is that 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 want to see the execution plan before you run the query and not afterward. You also may not want this behavior if you are writing a DELETE or an UPDATE statement because you would need to delete or update some data 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 does more than display the execution plan for a query. It 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 shown as well. With older releases of SQL*Plus, 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_$, are pseudoviews maintained by Oracle that contain ...