Previous Table of Contents Next

Chapter 10
Performance Tuning

Probably sooner than later, you’ll come across a block of code that performs very poorly. As an application developer, part of your job will be to improve the performance of code that doesn’t perform well. This chapter provides information about tuning SQL statements and PL/SQL blocks. Most of the material here is geared towards the use of the cost-based optimizer. Materials related to the use of the rule-based optimizer are clearly marked.

The bulk of your performance tuning work will be the analysis and modification of DML statements, but there are also some important tips presented for tuning PL/SQL as well.

Tuning SQL

DML statements (DELETE, INSERT, SELECT, and UPDATE) are the most common cause of performance problems in stored PL/SQL objects. There are a number of potential reasons why a given DML statement could perform poorly, including:

  Failure to use the proper indexes in a SELECT, UPDATE, or DELETE statement.
  Number and/or types of indexes degrading the performance of an INSERT, UPDATE, or DELETE statement.
  Statistics for the tables haven’t been updated recently (if using the cost-based optimizer).
  Lack of indexes usable by the SELECT or UPDATE statement (i.e., the index should not be used by the DML statement).
  The DML statement attempts to modify the value of an indexed column for a WHERE clause comparison.

Of course, there are other situations that can cause performance problems, but these are the most common reasons for DML statements to perform poorly.

In many instances, resolving a performance problem first requires that the performance bottleneck be identified. The EXPLAIN PLAN statement is an excellent tool for identifying SQL statements that perform poorly.

Using The EXPLAIN PLAN Statement

The EXPLAIN PLAN SQL statement is used to illustrate the steps that Oracle goes through to execute a specific DML statement. The use of the EXPLAIN PLAN statement is illustrated in Listing 10.1.

Listing 10.1 Using the EXPLAIN PLAN SQL statement.

SET statement_id = <statement_name>
INTO <plan_table>
FOR <SQL_statement>;

In this example, statement_name is a unique identifier for the SQL statement, plan_table is the name (possibly prefaced with a schema reference) of the table that holds the results (typically PLAN_TABLE), and SQL_statement is the SQL statement for which the EXPLAIN PLAN is being generated.

On a Unix system, the PLAN_TABLE table can be created by running the utlxplan.sql file from the $ORACLE_HOME/rdbms/admin directory.

This is the structure of the PLAN_TABLE table:

statement_id         varchar2 (30)
timestamp            date
remarks              varchar2 (80)
operation            varchar2 (30)
options              varchar2 (30)
object_node          varchar2 (30)
object_owner         varchar2 (30)
object_name          varchar2 (30)
object_instance      varchar2 (30)
object_type          varchar2 (30)
search_columns       number
id                   number
parent_id            number
position             number
other                long

The results of the EXPLAIN PLAN statement are written to this table and can be retrieved using the query in Listing 10.2.

Listing 10.2 Getting an EXPLAIN PLAN from the PLAN_TABLE table.

SELECT lpad (' ', 2 * (level - 1)) ||
       operation   || ' ' ||
       options     || ' ' ||
       object_name || ' ' ||
       decode (id, 0, 'Cost = ' || position) "EXPLAIN PLAN";
AND   statement_id  = <statement_name>
CONNECT BY PRIOR id = parent_id
AND   statement_id  = <statement_name>;

To use the code in this example, replace statement_name with the same value that was used for statement_name when generating the EXPLAIN PLAN. The query produces output that looks like this:


This output shows the series of operations performed by Oracle to resolve the statement and the total cost of those operations. High cost values are extremely undesirable.

TIP:  Using EXPLAIN PLAN With The Rule-Based Optimizer
Running an EXPLAIN PLAN on a statement that uses the rule-based optimizer will always show a cost of zero. However, you can still use the statement to identify poorly performing SQL statements by examining the operations that Oracle performs to resolve the query.

While using EXPLAIN PLAN alone can isolate performance bottlenecks, using TKPROF and EXPLAIN PLAN together will provide even more insights into how SQL statements perform.


TKPROF is a utility provided by Oracle that provides detailed statistics about the execution of a DML statement. The first step involved with running TKPROF is setting up a trace file.

Previous Table of Contents Next