Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter