Tips and Tricks: Oracle PLSQL Collection and Cursor Processing

Author: Ron Shaffer

February 2009

Situation
A batch process that executes every night at midnight has been running longer and longer. The base tables are adding data and for every row added the process seems to be getting slower.

Problem
Looking at the code of the process it was discovered that PLSQL cursors were being used for much of the work. Each loop in the process addressed exactly one row of data and all values were programmatically determined.

Solution
The solution is to modify the package to use PLSQL collections and bulk binding. What follows is a simple test which demonstrates the effectiveness of changing the code from using cursors to using bulk binds and collections. This script can be executed through SQLPLUS against any oracle 8i or above database.

/***************************************
Begin Script
***************************************/
set serverout on

drop table parts_test;
create table parts_test(part_number number(10,0), part_name varchar2(20));

DECLARE
TYPE NumTab IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
TYPE NameTab IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
pnums  NumTab;
pnames NameTab;
t1 NUMBER(10);
t2 NUMBER(10);
t3 NUMBER(10);
t4 NUMBER(10);

BEGIN
t1 := dbms_utility.get_time;
FOR j IN 1..100000 LOOP  -- load index-by tables
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
t2 := dbms_utility.get_time;
FOR i IN 1..100000 LOOP  -- use FOR loop
INSERT INTO parts_test VALUES (pnums(i), pnames(i));
END LOOP;
t3 := dbms_utility.get_time;
FORALL i IN 1..100000  -- use FORALL statement
INSERT INTO parts_test VALUES (pnums(i), pnames(i));
t4 := dbms_utility.get_time;
dbms_output.put_line('    Execution Time (Hundreths of seconds)   ');
dbms_output.put_line('---------------------------------------------');
dbms_output.put_line('Load Values in memory: ' || TO_CHAR(t2 - t1));
dbms_output.put_line('FOR loop: ' || TO_CHAR(t3 - t2));
dbms_output.put_line('FORALL statement: ' || TO_CHAR(t4 - t3));
dbms_output.put_line('Complete Duration: ' || TO_CHAR(t4 - t1));
dbms_output.put_line('Performance Ratio: ' || TO_CHAR((t3 - t2)/(t4-t3))||':1');
commit;
END;
/

Execution Time (Hundredths of seconds)
---------------------------------------------
Load Values in memory: 36
FOR loop: 1061
FORALL statement: 46
Complete Duration: 1143
Performance Ratio: 23.06521739130434782608695652173913043478:1

This demonstrates a 23:1 performance ratio of bulk collections over the standard cursor loop. You can see that if you could make a process 1/23rd the duration in which it currently executes that you can save a lot of time.

Tips
•    Any time a process needs to loop through records and make changes, look for an opportunity to make it a set operation or at least use bulk binds.
•    When you use bulk binds, be careful not to allocate too much memory by using the LIMIT clause.

If you need more information, contact Gary Codeluppi at 937-431-1026 x123 or visit the Ross Group Inc web site at www.rossgroupinc.com.