You are here:

Oracle/performace problem while processing only 11lacs record


Hi Suchitra,

we have 2 tables:header and detail table.
In header table we have primary key on item,country and segment.
In detail table we have multiple records for one item,country and segment on the basis of fiscal week.
ex. header data
item     country   segment
PPK09    hungry    gov
detail data
item     country    segment    fiscalweek
PPK09    hungry      gov        week1
PPK09    hungry      gov        week2
PPK09    hungry      gov        week52

now we have one proc in which we are opening the curor for header data and inside this cursor we are opening cursor for detail table.
for i in (select ....from header)
some function calls here
for j in (select .... from detail)
 call some functions here and then update one other table
end loop;
one update is here
end loop;

since the detail table will grow in multiple of header table, detail can have huge data.I felt that for huge amount of data ,using cursor wont be effective so i conveerted the above code in bulk collect but somehow there is not much performance gain.

for i in t_header.first..t_header.count loop
 function call here
for j in t_detail.first..t_detail.count loop
 call some functions here and then update one other table
end loop;
one update here
end loop;

is it because we are calling some functions inside loop so hence not able to avoid contxt switch everytime.
then what could be the best way to implement. as of now its taking 7 hours for 11lacs record.please suggest

Hi Reader,

I am not sure what is your exact problem and expectation. But you can try the FORALL feature of Bulk Collect (instead of for loop).

For more info on Performance tuning you can check the below link and use the appropriate method -

Hope this helps.




All Answers

Answers by Expert:

Ask Experts


Suchitra Joshi


I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader, Oracle Forms, Oracle Reports, and some basic dba and performance tuning activities.


15+ years of Oracle PL/SQL Development

B.Sc (Electronics), Diploma in Computer Applications (DCA)

Awards and Honors
Oracle PL/SQL Developer Certified Associate (OCA)
Oracle Database: SQL Certified Expert
Brainbench certifications in Oracle Administration, PL/SQL, Developer 2000

©2016 All rights reserved.