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_$, ...
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.