Previous | Table of Contents | Next |
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.
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:
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.
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.
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 |