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.
In this example, lets use BULK COLLECT to fetch information of all the applications present in an EBS instance.
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
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.
2 PLSQL Performance Tuning Bulk Collect Part 1
Similar to cursor attributes BULK COLLECT has %BULK_ROWCOUNT(n) that returns the number of rows affected in the nth DML statement of the FORALL statement, i.e. it will give the count of records affected in the FORALL statement for every single value from the collection variable. The term ‘n’ indicates the sequence of value in the collection, for which the row count is needed.
Oracle PL/SQL provides the functionality of fetching the records in bulk rather than fetching one-by-one. This BULK COLLECT can be used in ‘SELECT’ statement to populate the records in bulk or in fetching the cursor in bulk. Since the BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between database and PL/SQL engine.
The bulk collect concept loads the entire data into the target collection variable as a bulk i.e. the whole data will be populated into the collection variable in a single-go. But this is not advisable when the total record that needs to be loaded is very large, because when PL/SQL tries to load the entire data it consumes more session memory. Hence, it is always good to limit the size of this bulk collect operation.
BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at once.
In the above syntax, BULK COLLECT is used in collect the data from ‘SELECT’ and ‘FETCH’ statement.
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.
In this example, lets use BULK COLLECT to fetch information of all the applications present in an EBS instance.
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.
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.
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.
FAQ
What is the use of bulk collect in Oracle?
What is the max limit for bulk collect in Oracle?
What are the features of bulk collect?
