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.
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.