Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from 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_$, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

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

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

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata