 |
|
Tuning PL/SQL
Oracle Tips by
Burleson
|
There’s very little call for tuning a properly designed block of
PL/SQL. When performance tuning is necessary, most (if not all) of the
work goes into tweaking performance improvements out of individual SQL
statements. Still, there are several important design considerations
for PL/SQL blocks that can have a significant impact on performance,
most notably in the areas of using cursors and exception handling. The
use of cursors allows you to significantly reduce the overhead
required by your SELECT statements.
Using Cursors
In PL/SQL terms, a cursor is best described as a defined SELECT
statement that can be referenced in your code as a variable. Most
PL/SQL blocks contain at least one SELECT statement. This
statement is often included inside the body of the PL/SQL block, as
shown in Listing 10.3.
Listing 10.3 A SELECT statement inside the body of a
PL/SQL block.
DECLARE
vLastName varchar2 (20);
BEGIN
SELECT last_name
INTO vLastName
FROM STUDENTS
WHERE ssn = '999999999';
END;
This query returns a single row (at least, we’re assuming that
there is one distinct social security number per student). While
there’s nothing wrong with the SELECT statement itself, there
is a performance problem associated with its use inside the PL/SQL
block.
Oracle executes two fetches to return this single row of data. The
first fetch returns the row of data returned by the query. The second
fetch is performed to make sure that there are no more rows that
satisfy the conditions of the query. Any SELECT statement
inside a PL/SQL block will always perform an extra fetch for this
purpose.
This extra fetch can be avoided if the SELECT statement is
implemented by using a cursor, as shown in Listing 10.4.
Listing 10.4 Implementing SELECT statement
functionality by using a cursor.
DECLARE
CURSOR StudentName_cur
IS
SELECT last_name
FROM STUDENTS
WHERE ssn = '999999999';
BEGIN
OPEN StudentName_cur;
FETCH StudentName_cur INTO StudentName_rec;
CLOSE StudentName_cur;
END;
The same SELECT statement, when implemented with a cursor,
requires one less fetch than a standalone SELECT statement
within the PL/SQL block. However, there are a couple of “gotchas”
lurking behind the use of a cursor in this way:
• Using a
cursor like this always returns only the first row of a result set. If
the query could potentially return more than one row, using a cursor
like this may cause you to overlook data that you need to process or
an error condition that you should be handling.
• Unless you’ve
used a meaningful identifier to name your cursor, you’ll find that
debugging your routine is difficult because you must keep jumping to
the top of your code to look at your cursor declaration again.
Probably more important than the use of cursors is the wise use of
exception handling. Careful use of exceptions can reduce the amount of
conditional logic (IF-THEN statements, etc.) used in
your code, thus reducing the number of instructions that the CPU must
process.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
