 |
|
Tuning SQL
Oracle Tips by
Burleson
|
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.
EXPLAIN PLAN
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";
FROM PLAN_TABLE
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
this:
EXPLAIN PLAN
------------------------------------------------------------------
SELECT STATEMENT Cost = 13
MERGE JOIN
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.
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.
Using TKPROF
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. |
