Improve Oracle Insert Performance with BULKCOLLECT and FORALL

As specified by Steven Feuerstein into the Oracle Blog Website the bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.

Using BULK COLLECT plus FORALL instead of standard Insert statement to insert data improve performance dramatically, let’s me show you :

Here is a customer case on how using BULK COLLECT plus FORALL to improve Insert operations for very big tables (more than 1 billion of rows and more than 300Gb in size).

First of all, create a bigfile tablespace which will contain the data:

SQL> CREATE bigfile TABLESPACE tbs1 DATAFILE '+data' SIZE 310G;

Tablespace created.

Elapsed: 00:11:30.87

Next step is to create the table (empty) and move it to the bigfile tablespace:

CREATE TABLE "xxxx"."DBI_FK_NOPART"
( "PKEY" NUMBER(12,0) NOT NULL ENABLE,
"BOID" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"METABO" NUMBER(12,0) NOT NULL ENABLE,
"LASTUPDATE" TIMESTAMP (9) NOT NULL ENABLE,
"PROCESSID" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"ROWCOMMENT" VARCHAR2(15 CHAR) COLLATE "USING_NLS_COMP",
"CREATED" TIMESTAMP (9) NOT NULL ENABLE,
"CREATEDUSER" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"REPLACED" TIMESTAMP (9) NOT NULL ENABLE,
"REPLACEDUSER" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP",
"ARCHIVETAG" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP",
"MDBID" VARCHAR2(255 CHAR) COLLATE "USING_NLS_COMP",
"ITSFORECAST" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"BETRAG" NUMBER(15,2) NOT NULL ENABLE,
"ITSOPDETHERKUNFT" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP",
"ITSOPDETHKERSTPRM" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP",
"ITSFCKOMPPREISSEQ" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP",
"CLSFCKOMPPREISSEQ" NUMBER(12,0),
"ISSUMMANDENDPREIS" NUMBER(12,0) NOT NULL ENABLE,
"PARTITIONTAG" NUMBER(12,0) NOT NULL ENABLE,
"PARTITIONDOMAIN" VARCHAR2(4 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"FCVPRODKOMPPKEY" NUMBER(12,0),
"FCKVPRDANKOMPPKEY" NUMBER(12,0)
) ;

--MOVE TABLE TO BIGFILE TABLESPACE
ALTER TABLE "xxxx"."DBI_FK_NOPART" MOVE ONLINE TABLESPACE tbs1;

Load Data with BULK COLLECT and FORALL:

SQL> declare
type testarray is table of varchar2(3000) index by binary_integer;
v_PKEY testarray;
v_BOID testarray;
v_METABO testarray;
v_LASTUPDATE testarray;
v_PROCESSID testarray;
v_ROWCOMMENT testarray;
v_CREATED testarray;
v_CREATEDUSER testarray;
v_REPLACED testarray;
v_REPLACEDUSER testarray;
v_ARCHIVETAG testarray;
v_MDBID testarray;
v_ITSFORECAST testarray;
v_BETRAG testarray;
v_ITSOPDETHERKUNFT testarray;
v_ITSOPDETHKERSTPRM testarray;
v_ITSFCKOMPPREISSEQ testarray;
v_CLSFCKOMPPREISSEQ testarray;
v_ISSUMMANDENDPREIS testarray;
v_PARTITIONTAG testarray;
v_PARTITIONDOMAIN testarray;
v_FCVPRODKOMPPKEY testarray;
v_FCKVPRDANKOMPPKEY testarray;

cursor cu_cursor is select PKEY,BOID,METABO,LASTUPDATE,PROCESSID,ROWCOMMENT,CREATED,CREATEDUSER,REPLACED,REPLACEDUSER,ARCHIVETAG,MDBID,ITSFORECAST,BETRAG,
ITSOPDETHERKUNFT,ITSOPDETHKERSTPRM,ITSFCKOMPPREISSEQ,CLSFCKOMPPREISSEQ,ISSUMMANDENDPREIS,PARTITIONTAG,PARTITIONDOMAIN,FCVPRODKOMPPKEY,FCKVPRDANKOMPPKEY
FROM xxx.TableSource;

begin
dbms_output.put_line('start : '||to_char(sysdate,'dd.mm.rrrr hh24:mi:ss'));
open cu_cursor;

loop

fetch cu_cursor bulk collect into v_PKEY,v_BOID,v_METABO,v_LASTUPDATE,v_PROCESSID,v_ROWCOMMENT,v_CREATED,v_CREATEDUSER,v_REPLACED,v_REPLACEDUSER ,v_ARCHIVETAG,v_MDBID,v_ITSFORECAST,v_BETRAG,v_ITSOPDETHERKUNFT,
v_ITSOPDETHKERSTPRM ,v_ITSFCKOMPPREISSEQ ,v_CLSFCKOMPPREISSEQ,v_ISSUMMANDENDPREIS ,v_PARTITIONTAG,v_PARTITIONDOMAIN,v_FCVPRODKOMPPKEY,v_FCKVPRDANKOMPPKEY LIMIT 1000;

forall i in 1 .. v_PKEY.count

insert into xxx.DBI_FK_NOPART( PKEY,BOID,METABO,LASTUPDATE,PROCESSID,ROWCOMMENT,CREATED,CREATEDUSER,REPLACED,REPLACEDUSER ,ARCHIVETAG,MDBID,ITSFORECAST,BETRAG,ITSOPDETHERKUNFT,
ITSOPDETHKERSTPRM ,ITSFCKOMPPREISSEQ ,CLSFCKOMPPREISSEQ,ISSUMMANDENDPREIS ,PARTITIONTAG,PARTITIONDOMAIN,FCVPRODKOMPPKEY,FCKVPRDANKOMPPKEY )
values
( v_PKEY(i),v_BOID(i),v_METABO(i),v_LASTUPDATE(i),v_PROCESSID(i),v_ROWCOMMENT(i),v_CREATED(i),v_CREATEDUSER(i),v_REPLACED(i),v_REPLACEDUSER(i),v_ARCHIVETAG(i),v_MDBID(i),v_ITSFORECAST(i),v_BETRAG(i),v_ITSOPDETHERKUNFT(i),
v_ITSOPDETHKERSTPRM(i),v_ITSFCKOMPPREISSEQ(i),v_CLSFCKOMPPREISSEQ(i),v_ISSUMMANDENDPREIS(i),v_PARTITIONTAG(i),v_PARTITIONDOMAIN(i),v_FCVPRODKOMPPKEY(i),v_FCKVPRDANKOMPPKEY(i));

exit when cu_cursor%notfound;
end loop;

close cu_cursor;
dbms_output.put_line('end : '||to_char(sysdate,'dd.mm.rrrr hh24:mi:ss'));

end;
/
start : 15.11.2021 10:30:36
end : 15.11.2021 12:50:23

PL/SQL procedure successfully completed.

Elapsed: 02:19:46.80

Gather Statistics:

exec dbms_stats.gather_table_stats('xxx','DBI_FK_NOPART');

Add primary key and indexes (store it into bigfile tablespace) :

ALTER TABLE xxx.DBI_FK_NOPART ADD CONSTRAINT PK6951_1 PRIMARY KEY (PKEY) using index tablespace tbs1;

BEGIN
CREATE INDEX "xxx"."CLSFCKOMPPREISSEQ695_1" ON "xxx"."DBI_FK_NOPART" ("CLSFCKOMPPREISSEQ") TABLESPACE "TBS1";
CREATE INDEX "xxx"."ITSFCKOMPPREISSEQ695_1" ON "xxx"."DBI_FK_NOPART" ("ITSFCKOMPPREISSEQ") TABLESPACE "TBS1";
CREATE INDEX "xxx"."ITSFORECAST695_1" ON "xxx"."DBI_FK_NOPART" ("ITSFORECAST") TABLESPACE "TBS1" ;
CREATE INDEX "xxx"."IX_MDBID_xxx_1" ON "xxx"."DBI_FK_NOPART" ("MDBID") TABLESPACE "TBS1" ;
END;

Let’s check statistics of the table :

select owner,table_name,num_rows,blocks, last_analyzed from dba_tables where table_name = 'DBI_FK_NOPART';
OWNER TABLE_NAME      NUM_ROWS  BLOCKS    LAST_ANALYZED
XXX    DBI_FK_NOPART  1188403800 39871915 15.11.21

Conclusion :

With BULK COLLECT plus FORALL, I inserted more than 1 billion of rows in 02h19.

With standard Insert through a FOR LOOP statement, the Insert never finished, I stopped it after 15 hours of execution and after resizing muliple times the Undo tablespace due to “unable to extend tablespace…” error.