Bulk Binds

Oracle use two engine to process PLSQL block and subprogram. All SQL code is processed by SQL engine and PLSQL code is processed by PLSQL engine. When you run SQL code in the loop, oracle has to switch between SQL engine and PLSQL engine for each iteration. So there is overhead associated for each context switch. Bulk Bind is introduced in oracle8i to reduce the context switch.. This feature is enhanced in further versions...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> CREATE TABLE emp(
2 empno NUMBER(10),
3 empname VARCHAR2(50));

Table created.

SQL>
SQL> ALTER TABLE emp ADD (
2 CONSTRAINT emppk PRIMARY KEY(empno));

Table altered.

SQL>

The total time taken to insert the 50,000 records through FOR LOOP is .071 Minuts. Because, 50,000 context switch happens in the FOR LOOP.

SQL> set serveroutput on
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF EMP%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
8 t_tab(t_tab.last).empno := i;
9 t_tab(t_tab.last).empname := 'name:' To_Char(i);
10 END LOOP;
12 FOR i IN t_tab.first .. t_tab.last LOOP
13 INSERT INTO emp (empno, empname)
14 VALUES (t_tab(i).empno, t_tab(i).empname);
15 END LOOP;
16 COMMIT;
17 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
18 END;
19 /
Total time ... .071

PL/SQL procedure successfully completed.

SQL>

Here time taken to insert the 50,000 records through bulk binds is 0.015 Minuts. When we compared to above example, it is much faster.. since it is completed reduced the context switches between SQL engine to PLSQL engine.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 50000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name:' To_Char(i);
11 END LOOP;
12
13 FORALL i IN t_tab.first .. t_tab.last
14 INSERT INTO emp VALUES t_tab(i);
15 COMMIT;
16 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
17 END;
18 /
Total time ... .015

PL/SQL procedure successfully completed.

SQL>

Bulk binds can also improve the performance when loading collection from queries. BULK COLLECT INTO clause can collect the data into collections. In the below example, it took 0.021 Minuts to fetch 50,000 records with out bulk binds

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 CURSOR c_data IS
6 SELECT *
7 FROM emp;
8 BEGIN
9 FOR cur_rec IN c_data LOOP
10 t_tab.extend;
11 t_tab(t_tab.last).empno := cur_rec.empno;
12 t_tab(t_tab.last).empname := cur_rec.empname;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
15 END;
16 /
Total time ... .021

PL/SQL procedure successfully completed.

In the below case, it took 0.004 Minutes to fetch the data into collection. When we compared to above case, it is faster.. since it is using BULK COLLECT to reduce the context switch.

SQL>
SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 t_tab emp_tab := emp_tab();
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 BEGIN
6 SELECT empno,empname
7 BULK COLLECT INTO t_tab
8 FROM emp;
9 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
10 END;
11 /
Total time ... .004

PL/SQL procedure successfully completed.

SQL>

Oracle 9i Release 2 allows to use ROW key word to update the record. When we update, we can not use record type definition with bulk binds. The below case, we can not use bulk bind to improve the performance, since we are using record type definition. This is one restriction on this version(oracle9i R2).

SQL> DECLARE
2 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
3 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
4 t_tab emp_tab := emp_tab();
5 BEGIN
6 FOR i IN 1 .. 10000 LOOP
7 t_tab.extend;
9 t_tab(t_tab.last).empno := i;
10 t_tab(t_tab.last).empname := 'name' To_Char(i);
11 END LOOP;
13 FOR i IN t_tab.first .. t_tab.last LOOP
14 UPDATE emp
15 SET ROW = t_tab(i)
16 WHERE empno = t_tab(i).empno;
17 END LOOP;
18 COMMIT;
19 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
20 END;
21 /
Total time ... .012

PL/SQL procedure successfully completed.

SQL>
The below case, we are using bulk bind since, we are not using record type definition. The time took to update the 50,000 records is 0.007 Minuts. When we compare with above case, the performance is improved well.

SQL> DECLARE
2 TYPE empno_tab IS TABLE OF emp.empno%TYPE;
3 TYPE emp_tab IS TABLE OF emp%ROWTYPE;
4 l_start NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
5 t_empno empno_tab := empno_tab();
6 t_tab emp_tab := emp_tab();
7 BEGIN
8 FOR i IN 1 .. 10000 LOOP
9 t_empno.extend;
10 t_tab.extend;
11 t_empno(t_empno.last) := i;
12 t_tab(t_tab.last).empno := i;
13 t_tab(t_tab.last).empname := 'name ' To_Char(i);
14 END LOOP;
16 FORALL i IN t_tab.first .. t_tab.last
17 UPDATE emp
18 SET ROW = t_tab(i)
19 WHERE empno = t_empno(i);
21 COMMIT;
22 DBMS_OUTPUT.PUT_LINE('Total time ... 'ROUND(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2)/6
0,3));
23 END;
24 /
Total time ... .007

PL/SQL procedure successfully completed.

SQL>

When to use Bulk Binds: There is no univeral rule exists to dictate when to use Bulk binds. If PLSQL code reads only few hundered records, then you will not see significant performance improvement for bulk binds. When you read huge number of records and have multiple insert/update, then you can think of bulk binds. If you have the luxury of time, you can test your code both with and without bulk binds and decide to go for this feature.