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 EXPLAIN PLAN

EXPLAIN PLAN is a SQL statement that causes Oracle to report the execution plan it would choose for any SELECT, INSERT, UPDATE, or DELETE statement. An execution plan refers to the approach Oracle will take to retrieve the necessary data for a statement. One example of a plan would be to use an index to find the required rows. Another example of an execution plan would be to sequentially read all rows in the table. If you have a poorly-performing SQL statement, you can use EXPLAIN PLAN to find out how Oracle is processing it. With that information, you may be able to take some corrective action to improve performance.

When you use EXPLAIN PLAN, Oracle doesn’t display its execution strategy on the screen; instead, it inserts rows into a table. This table is referred to as the plan table , and you must query it properly in order to see the results. Of course, the plan table must exist, so if you’ve never used EXPLAIN PLAN before, you may need to create the plan table first.

Tip

Oracle occasionally adds columns to the plan table. If you have a plan table created using a previous version of Oracle, you may want to drop and recreate it, just to be sure you have the most up-to-date version.

Creating the Plan Table

Oracle provides a script to create the plan table. It is named UTLXPLAN.SQL , and it resides in the RDBMS/ADMIN directory for your database. Under Windows 95, for example, the script to create the plan table for Oracle8 will be C:\ORAWIN95\RDBMS80\ADMIN\UTLXPLAN.SQL ...

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