To improve PLSQL performance a feature of 8i, 9i and 10G are Oracle's BULK COLLECT and FORALL statements. Both statements implements a form of array processing inside PL/SQL
1. BULK COLLECT facilitates high-speed retrieval of data
2. FORALL greatly improves performance of INSERT, UPDATE , and DELETE operations.
The Oracle Database achieves significant performance gains with these statements by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.
BULK COLLECT your data
With BULK COLLECT , you fetch multiple rows into one or more collections, rather than individual variables or records.
Executing SQL statements in PLSQL programs causes a context switch between the PLSQL engine and the SQL engine. Too many context switches may degrade performance dramatically.
In order to reduce the number of these context switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the SQL engine and the PLSQL engine as collections.
Bulk binding is available for select, insert, delete and update statements.
When developing I usually use either a FOR loop or declares a cursor, opens it, fetch into and close it as a normal way of processing data on a row-by-row basis. Let me illustrate a simple example just to give you an idea on how BULK COLLECT can help you.
SQL> create table t_Turbo as select * from customer;
Table created.
SQL> select count(*) from t_Turbo;
COUNT(*)
----------
219856
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 rec1 c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into rec1;
8 exit when c1%notfound;
9
10 null;
11
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.25
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 type c1_type is table of c1%rowtype;
4 rec1 c1_type;
5 begin
6 open c1;
7
8 fetch c1 bulk collect into rec1;
9
10
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.32
As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.
The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 type c1_type is table of c1%rowtype;
4 rec1 c1_type;
5 begin
6 open c1;
7 loop
8 fetch c1 bulk collect into rec1 limit 200;
9 for i in 1..rec1.count loop
10 null;
11 end loop;
12 exit when c1%notfound;
13 end loop;
14
15
16 end;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.07
Turbocharged DML using FORALL
FORALL transfers data from a PL/SQL collection to the specified table using collections.
BULK COLLECT and FORALL are very helpful, not only in improving performance but also in simplifying the code you need to write for SQL operations in PL/SQL.
One of the best articles on FORALL has been written by Steven Feuerstein, in Oracle Magazine , and it includes all the new 10G stuff that's available with FORALL. I will leave you with Steven Feuerstein's article - enjoy reading it.
1. BULK COLLECT facilitates high-speed retrieval of data
2. FORALL greatly improves performance of INSERT, UPDATE , and DELETE operations.
The Oracle Database achieves significant performance gains with these statements by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.
BULK COLLECT your data
With BULK COLLECT , you fetch multiple rows into one or more collections, rather than individual variables or records.
Executing SQL statements in PLSQL programs causes a context switch between the PLSQL engine and the SQL engine. Too many context switches may degrade performance dramatically.
In order to reduce the number of these context switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the SQL engine and the PLSQL engine as collections.
Bulk binding is available for select, insert, delete and update statements.
When developing I usually use either a FOR loop or declares a cursor, opens it, fetch into and close it as a normal way of processing data on a row-by-row basis. Let me illustrate a simple example just to give you an idea on how BULK COLLECT can help you.
SQL> create table t_Turbo as select * from customer;
Table created.
SQL> select count(*) from t_Turbo;
COUNT(*)
----------
219856
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 rec1 c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into rec1;
8 exit when c1%notfound;
9
10 null;
11
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.25
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 type c1_type is table of c1%rowtype;
4 rec1 c1_type;
5 begin
6 open c1;
7
8 fetch c1 bulk collect into rec1;
9
10
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.32
As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.
The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.
SQL> declare
2 cursor c1 is select object_name from t_Turbo;
3 type c1_type is table of c1%rowtype;
4 rec1 c1_type;
5 begin
6 open c1;
7 loop
8 fetch c1 bulk collect into rec1 limit 200;
9 for i in 1..rec1.count loop
10 null;
11 end loop;
12 exit when c1%notfound;
13 end loop;
14
15
16 end;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.07
Turbocharged DML using FORALL
FORALL transfers data from a PL/SQL collection to the specified table using collections.
BULK COLLECT and FORALL are very helpful, not only in improving performance but also in simplifying the code you need to write for SQL operations in PL/SQL.
One of the best articles on FORALL has been written by Steven Feuerstein, in Oracle Magazine , and it includes all the new 10G stuff that's available with FORALL. I will leave you with Steven Feuerstein's article - enjoy reading it.
