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

Chapter 8. Tuning and Timing

Oracle offers two features that can be used from SQL*Plus to monitor and improve the performance of your scripts and SQL statements. These two features are SQL*Plus timers and the EXPLAIN PLAN command.

SQL*Plus has a timing feature built into it that can be used to monitor the length of time it takes to execute a SQL command, a PL/SQL block, or any other part of a script. To measure the time it takes to execute a SQL statement, you start a timer prior to executing the statement, then display the value of the timer immediately after the statement is executed.

The EXPLAIN PLAN command, although it is a SQL command, not a SQL*Plus command, is often used from SQL*Plus. EXPLAIN PLAN can be used to find out exactly how Oracle intends to execute any given SQL query. It will tell you, for example, whether or not an index will be used, and what the name of that index will be. Once you know how Oracle intends to execute the query, you can use hints to influence or alter Oracle’s default plan based on your knowledge of the data. A hint is a command to the optimizer that is embedded in a comment within a SQL query. The optimizer is the part of Oracle that determines how best to retrieve the data required by a SQL statement.

This chapter is not intended to be an exhaustive reference for tuning SQL statements. Several good books have been written on this subject. What this chapter does provide is a quick overview of the mechanics of tuning and a convenient summary ...

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