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



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.

   vLastName   varchar2 (20);
   SELECT last_name
   INTO   vLastName
   WHERE  ssn = '999999999';

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.

   CURSOR StudentName_cur
   SELECT last_name
   WHERE  ssn = '999999999';
   OPEN StudentName_cur;
   FETCH StudentName_cur INTO StudentName_rec;
   CLOSE StudentName_cur;

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