Bulk Binds and Collects in PL/SQL - Part 2
July 21, 2004
Avoiding performance issues for huge amounts of data in PL/SQL collection
The SQL engine will bulk bind all the values present for a \column in a table before returning the collection to the PL/SQL engine. If the amount of data being retrieved is immense, this fact itself, will result in degradation of performance. An alternative is to retrieve one set of records at a time for processing. This can be achieved by using an applicable WHERE condition or by using the ROWNUM pseudo-column. If using the FETCH INTO statement, then the LIMIT clause can also be used to limit the number of rows.
... begin select trx_id bulk collect into l_pndidr from mtl_pending_trx where rownum < 5001; ... end;
In the above example, if processing is taking place every 5000 records, there are chances that the same records may be processed multiple times. To avoid this, a proper query criterion can be mentioned based on a column in the table, along with or without the ROWNUM clause. For example, we can specify that the employee number be between a particular range. This range will change or take up the next set of higher values every time the bulk collect query is invoked in a loop.
Alternatively, the LIMIT option in the FETCH command can also be used. The idea is to process huge amounts of data in chunks, with the FETCH-LIMIT option; this could be achieved in a loop. This puts less overhead on the memory.
If too much data is loaded, it can result in the ORA-4030 error due to shortage of memory.
Below is a simple example that uses the LIMIT option to pick up a specified number of records. This statement can be executed in a loop resulting in a specified set of records being processed at a time.
declare type pndidr is table of mtl_pending_trx.trx_id%type index by binary_integer; type pndqty is table of mtl_pending_trx.trx_qty%type index by binary_integer; type pndval is table of mtl_pending_trx.trx_cost%type index by binary_integer; l_pndidr pndidr; l_pndqty pndqty; l_pndval pndval; cursor cr_rec is select trx_id, trx_qty, trx_cost from mtl_pending_trx; begin open cr_rec; fetch cr_rec bulk collect into l_pndidr, l_pndqty, l_pndval limit 200; close cr_rec; ... for i in l_pndidr.first .. l_pndidr.last loop dbms_output.put_line(l_pndidr(i)); dbms_output.put_line(l_pndqty(i)); dbms_output.put_line(l_pndval(i)); end loop; end; /
The bottom line here is that you need to take care of the amount of data that is being brought into the memory for processing in a collection. The cut-off limits depend on the memory allocations available. For example at my site, I normally follow the condition that collection types should have an approximate loading range of 5000 records (this could be more or less for you). If processing needs to be done for more than 5000 records then we resort to Temporary Tables. I arrived at this range after users complained about overall system performance when numerous users worked on the same processes that involved bulk collections. You do not need to wait for processes to go to production before identifying performance issues. You can start of with a safe limit and then monitor the performances at peak times.
Using index-by tables is the traditional multi-column collection method and still widely used. These are also known as Associative Arrays now. Nested tables and VARRAYs are the new collection methods made available in recent releases. Bulk collection can also be used to populate a collection of records with multiple columns.
In the below example, multi-column records are loaded into a collection:
declare type objtab is table of dba_objects%rowtype; l_objtab objtab; begin select * bulk collect into l_objtab from dba_objects where object_type = 'INDEX'; for i in l_objtab.first .. l_objtab.last loop dbms_output.put_line('index name:' || l_objtab(i).object_name); end loop; end; /
Loading data into multi-column collections was not supported in earlier versions of Oracle. I get the following error in Oracle 220.127.116.11.1, but it works fine in higher releases. I tested the above in Oracle 10g.
SQL> declare 2 type objtab is table of dba_objects%rowtype; 3 l_objtab objtab; 4 begin 5 select * bulk collect 6 into l_objtab 7 from dba_objects 8 where object_type = 'INDEX'; 9 10 for i in l_objtab.first .. l_objtab.last loop 11 dbms_output.put_line('index name:' || l_objtab(i).object_name); 12 end loop; 13 end; 14 / into l_objtab * ERROR at line 6: ORA-06550: line 6, column 10: PLS-00597: expression 'L_OBJTAB' in the INTO list is of wrong type ORA-06550: line 7, column 3: PL/SQL: ORA-00904: invalid column name ORA-06550: line 5, column 3: PL/SQL: SQL Statement ignored
You can also use VARRAYs with bulk binding options. Below is a simple example:
declare type pndtab is varray(200) of mtl_pending_trx%rowtype; l_pndtab pndtab; cursor cr_rec is select trx_id, trx_qty, trx_cost from mtl_pending_trx; begin open cr_rec; fetch cr_rec bulk collect into l_pndtab; close cr_rec; ... for i in l_pndtab.first .. l_pndtab.last loop dbms_output.put_line(l_pndtab(i).trx_id); end loop; end; /
The example below processes records in a loop, using the FETCH-LIMIT option. Please note that every time the FETCH command is executed, it returns the next 500 records and so on. The EXIT statement is present at the end of the loop so that the last batch of records is processed before exiting the loop.
declare type segtab is table of dba_segments%rowtype; type instab is table of dba_segments.segment_name%type index by binary_integer; l_segtab segtab; l_instab instab; cursor cr_rec is select * from dba_segments where segment_type = 'INDEX'; begin open cr_rec; loop l_segtab.delete; fetch cr_rec bulk collect into l_segtab limit 500; if l_segtab.count > 0 then l_instab.delete; for i in l_segtab.first .. l_segtab.last loop ... l_instab(i) := l_segtab(i).segment_name; end loop; forall i in l_segtab.first .. l_segtab.last insert into am_segments(segment_name) values(l_instab(i)); end if; exit when cr_rec%notfound; end loop; close cr_rec; end; /
Oracle provides a composite attribute that is designed for use with the FORALL statement. For each DML activity carried out in the FORALL statement, a record will be stored in this attribute that provides information about the rows processed. If no data is processed the attribute returns 0 for the concerned record. The example below will clarify the use of this attribute.
declare type instab is table of dba_users.username%type index by binary_integer; l_instab instab; begin select username bulk collect into l_instabf from dba_users; forall i in l_instab.first .. l_instab.last insert into am_segments(segment_name) select segment_name from dba_segments where owner = l_instab(i); dbms_output.put_line('Total count: ' || sql%rowcount); for i in l_instab.first .. l_instab.last loop dbms_output.put_line(l_instab(i) || ': ' || sql%bulk_rowcount(i)); end loop; end; /
The above block returns the output shown below. The SQL%ROWCOUNT attribute returns the total records processed in the FORALL statement and the SQL%BULK_ROWCOUNT attribute returns the records processed for each individual record in the collection.
Total count: 4010 SYS: 1408 OUTLN: 6 DBSNP: 4 SYSMAN: 735 MGMT_VIEW: 0 WK_TEST: 34 MDSYS: 86 ORDSYS: 8 CTXSYS: 74 ANONYMOUS: 0 ...
The above-mentioned bulk binds and collects are basically PL/SQL development features. Developers should make these a regular part of coding. In the long run, such features will immensely benefit the over all performance. Care should be taken to carry out collection processing without excessive hits on memory.