High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

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.

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.

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.