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.
Multi-column collections
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 9.0.1.1.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;
/
%BULK_ROWCOUNT
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
…
Conclusion
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.
