Oracle Tips by
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
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
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
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
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
Listing 10.1 Using the EXPLAIN PLAN SQL statement.
SET statement_id = <statement_name>
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
This is the structure of the PLAN_TABLE table:
statement_id varchar2 (30)
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)
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
SELECT lpad (' ', 2 * (level - 1)) ||
operation || ' ' ||
options || ' ' ||
object_name || ' ' ||
decode (id, 0, 'Cost = ' || position) "EXPLAIN PLAN";
START WITH id = 0
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
SELECT STATEMENT Cost = 13
TABLE ACCESS FULL STUDENTS
TABLE ACCESS BY ROWID STUDENTS
INDEX UNIQUE SCAN STUDENTS_SSN
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.
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.