Archive for April, 2009

Design Considerations – Enhanced Bulk Binding in PL/SQL

April 22, 2009

The Bulk Binding is relatively old technology,
but unfortunatelly rarely used by developers.
Basically the Bulk Binding is buffer technology.
Whenever you want to execute an SQL Statement or Fetch statement in loop,
please consider the Bulk Binding.
When you communicate withmthe SQL Engine from your PL/SQL program,
then Oracle creates a - so called - context switch.
This consumes pretty amount of time in loop.
We can reduce it applying the buffer technology.
Lets see the following example.
1. We create a relatively big table (ca.1 200 000 rows)

CREATE TABLE big_emp(empno,last_name,first_name,salary)
AS
SELECT ROWNUM,E.last_name||ROWNUM,E.first_name||ROWNUM,E.salary
FROM employees E,employees D,employees F;

2. Run this procedure, which uses the traditional technology:
CREATE OR REPLACE PROCEDURE trad_fetch_open
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC trad_fetch_open __will use more than 15 seconds CPU time!

3. We can use explicit Bulk Binding with the following procedure.
   Observe that we can control the size of the buffer with the LIMIT clause!
CREATE OR REPLACE PROCEDURE
BULK_LIMIT(tname VARCHAR2,rows NUMBER := 10)
IS
type emp_cur_type IS REF CURSOR ;
type c_type is table of  BIG_EMP%rowtype;
c1 emp_cur_type; j number:=0; emp c_type;
S NUMBER:=0;
n1 number;
n2 number;
stmt CLOB;
BEGIN
  stmt:='SELECT * FROM '||tname;
  n1:=dbms_utility.get_cpu_time;
  OPEN c1 FOR stmt;
  LOOP
      FETCH c1 BULK COLLECT INTO EMP LIMIT rows;
      EXIT WHEN c1%NOTFOUND and emp.count=0;
      FOR I IN 1..EMP.COUNT LOOP
        S:=S+ EMP(I).SALARY;
      END LOOP;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
EXEC BULK_LIMIT('BIG_EMP',1000)
We can see that with relatively small buffer size(for example with 1000)
we can achieve very good result.
If we increase the size above 1000 the performance can be worse
according to enlarged memory handling!

4. In Oracle 10g R2 and later we can use implicit Bulk Binding.
Using the traditional FOR rec_var IN cursor_name LOOP statement
we can achieve the Bulk Binding with appropriate
setting of PLSQL_OPTIMIZE_LEVEL.
Consider the following example:
a. ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =2;
   Note this is the default value of this parameter in Oracle 10g and later.
b. Create the following procedure:
CREATE OR REPLACE PROCEDURE bulk_fetch_for
IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;  n1 number;   n2 number;
BEGIN
 n1:=dbms_utility.get_cpu_time;
 FOR EMP2 IN C1 LOOP
   S:=S+ EMP2.SALARY;
 END LOOP;
 n2:=dbms_utility.get_cpu_time;
 DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
c. EXEC bulk_fetch_for -- will use less than 2 seconds CPU time!
   This is called AUTOBULKIFICATION!
d. Issue the following statement:
   ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL =1;
e. Recompile the bulk_fetch_for procedure!
   ALTER PROCEDURE  bulk_fetch_for COMPILE;
f. EXEC bulk_fetch_for -- will use more than 15 seconds CPU time!

g. If we use the traditional OPEN FETCH technology we will get poor performance
   regardless the value of the PLSQL_OPTIMIZE_LEVEL parameter
CREATE OR REPLACE PROCEDURE trad_fetch_open IS
CURSOR C1 IS SELECT * FROM BIG_EMP;
S NUMBER:=0;
n1 number;
n2 number;
EMP2 BIG_EMP%ROWTYPE;
BEGIN
  n1:=dbms_utility.get_cpu_time;
  OPEN c1;
  LOOP
    FETCH c1 INTO EMP2 ;
    EXIT WHEN c1%NOTFOUND ;
    S:=S+ EMP2.SALARY;
  END LOOP;
  CLOSE c1;
  n2:=dbms_utility.get_cpu_time;
  DBMS_OUTPUT.PUT_LINE('diff:'||to_char((n2-n1)/100)||'S:='||S);
END;
/
h. EXEC trad_fetch_for -- will use more than 15 seconds CPU time!
The conclusion is:
- Use the explict Bulk Binding if You want specify the size of the buffer.
- Use the implicit Bulk Binding if You want to rely on the Oracle
  to rewrite your program to improve the performance.