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.

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.

» See All Articles by Columnist Amar Kumar Padhi