Hi,

This month I’ll focus on a couple of PL/SQL performance tips that will include how to utilize collections to radically increase row processing and a simple but useful coding tip on the benefits and pitfalls of some easy PL/SQL cursor coding techniques.

Cursor Coding Tips

Let’s start with a couple of simple techniques that most of us use to save coding.  Part 1 (click here to review Part 1) of this series discussed using %ROWTYPE to get the column names and attributes easily into your program.  I called it runtime inheritance.  When the program hits this, it does a SELECT statement to retrieve these names and attributes.  Well, you can select * into this rowtype, as I indicated.

Tip 1

If your table has lots of columns and you only need a few, don’t code the SELECT * but code the specific columns. Toad makes it very easy to name the cursor variable or the table name and it will do its ‘insites’ where a popup will appear and let you pick the column names.  IF you do the SELECT * and only use a few of the columns, you are incurring a ton of network traffic for just a little bit of information.  The network is the slow part anymore.

Bulk Bindingand Row Processing

PL/SQL passes all SQL to the SQL engine for processing.  This causes a context switch where PL/SQL will wait for the result set back from the SQL engine for each SQL statement processed.

Passing any kind of variable to SQL is called binding.  The variables in the SQL statement are called bind variables.  Passing an entire collection to a SQL statement in one step is called bulk binding.  Bulk binding is very quick because the SQL is processed with very few context switches.  The larger the collection, the more efficient bulk binding becomes.

This section will discuss various types of bulk binding such as:

  • Bulk Collect – populates collections very quickly from SQL or Return clauses
  • FORALL – performs DML based on contents of collections
    • IN-Bound – binding from a collection
    • OUT-Bound – bulk collect return clause

Bulk collect works with

  • SELECT  … INTO clauses
  • FETCH … INTO clauses
  • RETURNING clauses from DML

The number of elements returned to a collection can be controlled using the LIMITS syntax.

Bulk Collect works equally well with all the collection types.

Bulk Collect initializes and extends the Associative Array and the Nested Table collections automatically.  Bulk Collect cannot automatically extend a VARRAY collection, however, this unit will illustrate the LIMIT command and a loop can easily be setup to load and extend a VARRAY as well.

For empinfo_rec IN ‘select * from empinfo’

   LOOP
      ***your code here***
   END LOOP;

The above code is used to do what I call ‘row at a time’ processing.  This is an implicit cursor loop.  PL/SQL creates the cursor, opens, reads, and closes the cursor for you.  This code is fine when you are only looping through maybe a hundred rows or less.  Oracle10 converted this to a cursor using bulk binding!  So, technically, you don’t have to fix this code, Oracle fixed it for you.

Tip 2

I would use the bulk collect method below anytime I’m accessing more than just a handful of rows.

TYPE EMP_INFO_AA_TYPE IS TABLE OF
      USER0.EMP_INFO%ROWTYPE             
      INDEX BY BINARY_INTEGER;
  
EMPNO_AA_Table  EMP_INFO_AA_TYPE;
 
TYPE EMP_INFO_NT_TYPE IS TABLE OF USER0.EMP_INFO%ROWTYPE;             
 
EMPNO_NT_TABLE  EMP_INFO_NT_TYPE := EMP_INFO_NT_TYPE();
 
BEGIN
  
   DBMS_OUTPUT.PUT_LINE('User0 Timer Starts');
 
   SELECT * FROM user0.emp_info
   BULK COLLECT into EMPNO_AA_Table;

 Notice in the above code the key work ‘BULK COLLECT into EMPNO_AA_Table’ syntax.  This will execute the SQL statement and populate all the returned rows into the array in one database action.  All other syntax remains the same.

Bulk Collect Timing Example

The illustration on the left is not using bulk collect.  In this example, using the bulk collect syntax on the right to load these 15000 rows was six times faster.

DECLARE
   TYPE Emp_TYPE IS TABLE OF EMP.ENAME%TYPE;
   EMP_TABLE Emp_TYPE;
   CURSOR C_EMP IS
      SELECT ENAME FROM EMP;
     
BEGIN
   OPEN C_EMP;
   FETCH C_EMP BULK COLLECT INTO EMP_TABLE;
   CLOSE C_EMP;
   DBMS_OUTPUT.PUT_LINE(EMP_TABLE.COUNT || 'Rows Fetched');
   FOR i in EMP_TABLE.FIRST .. EMP_TABLE.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE(EMP_TABLE(i));
   END LOOP;
END;
/

You can use Bulk Collect works equally well with a FETCH command.  This method gives you a little better control over the cursor perhaps.  Maybe you are converting other code to use this method and maybe this will allow for fewer changes to your code.

DECLARE
  TYPE Emp_ENAME    IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
  
   EMPNO_TABLE Emp_ENAME;
     
   CURSOR C_EMP IS
      SELECT ENAME FROM EMP;
     
BEGIN
   OPEN C_EMP;
   LOOP
     FETCH C_EMP
        BULK COLLECT INTO EMPNO_TABLE
        LIMIT 1000;
     EXIT WHEN EMPNO_TABLE.COUNT = 0;

—  Do some additional PL/SQL processing perhaps

     DBMS_OUTPUT.PUT_LINE(C_EMP%ROWCOUNT || ' Rows Fetched so far');
 
   END LOOP;
    
   CLOSE C_EMP;
  
END;
/

This bulk collect illustrates the use of the LIMIT clause.  This loop will bring back 1000 rows at a time.  The <collection>.COUNT variable will contain the number of rows returned and when this variable is 0, then all the rows will have been returned.  If you just check the cursor variable %NOTFOUND to exit, you might miss some rows.

Tip 3

Even a limit of 100 rows will produce better performance than just returning a single row at a time.

Why is this important?  Say you have a longer row size and you are building your collection off of a %ROWTYPE type syntax.  You could make your PL/SQL routine larger than your assigned program global area will allow…causing your Oracle session to incur swapping or paging…an operating system memory management technique that will greatly slow your code execution.

Program Global Area Sizing

This SQL statement shows the total memory allocated and used by all PGA’s on a particular Oracle database.

Collections, like cursors, are created and maintained in the user’s Program Global Area, or PGA.  Each user gets one of these memory structures upon successful logon.  The size of the collection is the element type * the number of elements.

Make sure your ‘PGA inuse’ is not larger than your ‘PGA allocated’ while your code is executing.  You should check for this while coding and testing your code, not in production.

As always, you can ask me for the scripts and sample code.

Next in the series is using bulk binding to put rows back to the database super fast!

I hope you find these tips useful in your day to day use of the Oracle RDBMS.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com

About the Author

Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Start the discussion at forums.toadworld.com