Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

Chapter 12. Tuning and Timing

Oracle offers two features you can invoke from SQL*Plus to monitor
and improve the performance of your
scripts and
SQL statements: SQL*Plus timers
and the
EXPLAIN PLAN command.

SQL*Plus has a
built-in timing feature that can be
used to monitor the length of time it takes to execute a SQL
statement, 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 and display the value of the timer
immediately after the statement is executed.

EXPLAIN PLAN, although it is a SQL statement and not a SQL*Plus
command, is often issued from SQL*Plus. EXPLAIN PLAN can be used to
find out how Oracle intends to execute any given SQL query. It will
tell you, for example, whether 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.
This chapter provides a quick overview for SQL*Plus users of the
mechanics of tuning and a convenient summary of the hints available
to you.