What is BULK COLLECT? How and Why do we need to use it?

Before understanding about BULK COLLECT, lets see how a PL/SQL code is executed. Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL engine. When ever there is a need to process an SQL statement, a context switch happens between PL/SQL and SQL engines.

Imagine a cursor with a SELECT statement which retrieves 1000 rows, in such scenario a context switch will happen for 1000 times which consumes lot of CPU resources and leads to a performance issue. BULK COLLECT is one of the way to solve this problem.

BULK COLLECT is one of the way of fetching bulk collection of data. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them into a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, context switch happens only once. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

Example

In this example, lets use BULK COLLECT to fetch information of all the applications present in an EBS instance.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

DECLARE
   TYPE xx_rec IS RECORD (
      col1   fnd_application.APPLICATION_ID%TYPE,
      col2   fnd_application.APPLICATION_SHORT_NAME%TYPE,
      col3   fnd_application.PRODUCT_CODE%TYPE,
      col4   fnd_application.BASEPATH%TYPE
   ); --Record Type
 
   TYPE xx_tab IS TABLE OF xx_rec
      INDEX BY BINARY_INTEGER; --Table Type
 
   v_tab   xx_tab; --This is a type of Collection
 
   --
   --Cursor to list all applications in EBS instance
   --
   CURSOR cur
   IS
      SELECT application_id, application_short_name, product_code, basepath
        FROM fnd_application;
BEGIN
 
   OPEN cur;
 
   FETCH cur
   BULK COLLECT INTO v_tab; --BULK COLLECT usage
 
   CLOSE cur;
 
   FOR l_index IN v_tab.FIRST .. v_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line (   v_tab (l_index).col1
                            || ' '
                            || v_tab (l_index).col2
                            || ' '
                            || v_tab (l_index).col3
                            || ' '
                            || v_tab (l_index).col4
                           );
   END LOOP;
END;

Note: Remember that collections are held in memory, so doing a bulk collect from a large query could occupy most of your memory considerably leading to performance problem. Instead you can limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. Below is an example of usage of LIMIT clause

   OPEN cur;
 
   FETCH cur
   BULK COLLECT INTO v_tab LIMIT 100; --limiting BULK COLLECT to 100 records each per a context switch 
 
   CLOSE cur;

More in detail of how memory consumption happens when Collections are used:
Memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.