performance drag because of context switching
I need some ideas on how to boost the performance of one procedure. The procedure works like this:
create or replace procedure proc123 is
-- define cursor and type
cursor c1 is select columnA from tableA;
type p_array is table of tableB.columnA%type index by pls_integer;
v_array p_array;
-- some variables
begin
open c1;
loop
fetch c1 bulk collect into v_array limit 1000;
exit when v_array.count = 0;
for i in v_array.first..v_array.last loop
-- some manipulation and data processing
insert into global_temp_table
values(a, b);
end loop;
end loop;
create or replace procedure proc123 is
-- define cursor and type
cursor c1 is select columnA from tableA;
type p_array is table of tableB.columnA%type index by pls_integer;
v_array p_array;
-- some variables
begin
open c1;
loop
fetch c1 bulk collect into v_array limit 1000;
exit when v_array.count = 0;
for i in v_array.first..v_array.last loop
-- some manipulation and data processing
insert into global_temp_table
values(a, b);
end loop;
end loop;
0