Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Raja.

Asked: May 20, 2020 - 6:02 am UTC

Last updated: May 20, 2020 - 9:50 am UTC

Version: 18C

Viewed 1000+ times

You Asked

Hello Experts,

We have a package with object of 50 variables. Package will be invoked with 400 to 500 records with input object and We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequence. This whole process takes much time and we are trying to do some optimization over it. Can you please let me know how can we improve the performance of the package.

and Chris said...

To provide effective help here, we really need to see your code! Along with performance metrics, breaking down how long it takes to execute each part. Otherwise we're just guessing.

I discussed ways to get the performance profile in yesterday's SQL Office Hours; the recording will be available for this "soon" at:

https://asktom.oracle.com/pls/apex/f?p=100:551::::RP,551:P551_CLASS_ID:7563

That said, you almost certainly want to address this:

We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequence

SQL inside loops => SLOOOOOOOOOWWWWWWWW!

Either you want to insert the query directly:

insert into ....
  select ...


Or use bulk collection:

select ... bulk collect into ....

forall i in 1 .. recs.count 
  insert ....


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library