Bulk Insert Using ForALL into Clob Exhibits Bad Performance Prior to 11.2 (Doc ID 1089969.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

Inserting into to a Clob column using Forall takes a while (30 min + ) with even small set of data using sample tables in both 10.2.0.4 and 11.1.0.7. The same code takes about 1 min  in 11.2.0.1


CREATE TABLE Test_AUDIT(COMPLAINT_AUDIT_ID NUMBER(15),
RECORD CLOB,
ACTION VARCHAR2(1 BYTE));

CREATE TABLE Test_METRO( METRO_ID NUMBER(15));
begin
for i in 1..3000 loop
insert into Test_METRO values (i+1000);
end loop;
commit;
end;
/

select count(*) from Test_METRO;

set timing on
delete from Test_AUDIT;
commit;
set serveroutput on
DECLARE
type audit_tab is table of Test_AUDIT%rowtype;
l_audit_tab2 audit_tab;
l_start PLS_INTEGER;
l_bet PLS_INTEGER;
l_end PLS_INTEGER;

BEGIN
dbms_output.put_line('Hi there');
l_start := DBMS_UTILITY.get_cpu_time;
select METRO_ID, 'test','I'bulk collect into l_audit_tab2 from Test_METRO;
dbms_output.put_line('count ' || l_audit_tab2.count);
l_bet := DBMS_UTILITY.get_cpu_time;
DBMS_OUTPUT.put_line ('Elapsed time for bulk fetch ' || TO_CHAR (l_bet -
l_start));

forall i in l_audit_tab2.first .. l_audit_tab2.last
insert into Test_AUDIT values l_audit_tab2(i);

l_end := DBMS_UTILITY.get_cpu_time;
DBMS_OUTPUT.put_line ('Elapsed time for bulk insert ' || TO_CHAR (l_end -
l_bet));
DBMS_OUTPUT.put_line ('Total Elapsed time ' || TO_CHAR (l_end - l_start));
END;
/


In 11.2.0.1
----------
Hi there
count 3000
Elapsed time for bulk fetch 34
Elapsed time for bulk insert 9045
Total Elapsed time 9079

PL/SQL procedure successfully completed.

Elapsed: 00:01:31.49

In 11.1.0.7
---------
Hi there
count 3000
Elapsed time for bulk fetch 235
Elapsed time for bulk insert 191998
Total Elapsed time 192233

PL/SQL procedure successfully completed.

Elapsed: 00:34:38.73

In 10.2.0.4
-------
Hi there
count 3000
Elapsed time for bulk fetch 135
Elapsed time for bulk insert 208713
Total Elapsed time 208848

PL/SQL procedure successfully completed.

Elapsed: 00:37:36.62

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms