Home » SQL & PL/SQL » SQL & PL/SQL » BULK collect performance is slow
BULK collect performance is slow [message #226287] Fri, 23 March 2007 03:15 Go to next message
vin_odks
Messages: 153
Registered: July 2006
Location: -
Senior Member
Hi,

After experimenting 3 million records (INSERTS) using BULK COLLECT..and FORALL constructs, we found that these were very slow as compared to normal per record inserts and also rolled back most of the records.


The programs are run in parallel batches (say each 3 lakh records per batch)

Is there any method of improving the performance and preventing rollbacks.

Please advise

Regards
Vinod
Re: BULK collect performance is slow [message #226289 is a reply to message #226287] Fri, 23 March 2007 03:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How large were your bulklimits?
Why were the records rolled back?
Re: BULK collect performance is slow [message #226321 is a reply to message #226287] Fri, 23 March 2007 04:42 Go to previous messageGo to next message
vin_odks
Messages: 153
Registered: July 2006
Location: -
Senior Member
Bulk limits was 10000

And almost 60%of them got rolled back.

Regards
Vinod
Re: BULK collect performance is slow [message #226322 is a reply to message #226321] Fri, 23 March 2007 04:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
WHY were they rolled back?!
Re: BULK collect performance is slow [message #226463 is a reply to message #226322] Fri, 23 March 2007 20:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That array size is a bit big IMO depending on your memory and record size. If the array does not fit into memory then Oracle has to page it to disk.

The benefits of reduced context switching beyond an array size of 1000 are marginal at best.

But having said that, I concur with the others, the rolled back rows are a far greater problem. Why don't you load the rows into a Global Temporary Table and the MERGE them into the table and discard updates (assuming you are using 10g and the rolled back rows are failing on unique keys).

Ross Leishman
Re: BULK collect performance is slow [message #227521 is a reply to message #226287] Wed, 28 March 2007 08:13 Go to previous messageGo to next message
vin_odks
Messages: 153
Registered: July 2006
Location: -
Senior Member
Hi,

We are using the Oracle 9i version.
Can there be any useful parallel hints used alongwith the DML (INSERT)statement after the FORALL constructs ?

We would be changing the limit to 1000 and perform the test

Thanks for the suggestions.

Regards
Vinod
Re: BULK collect performance is slow [message #227523 is a reply to message #227521] Wed, 28 March 2007 08:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And still no answer...
Re: BULK collect performance is slow [message #269830 is a reply to message #226287] Mon, 24 September 2007 15:24 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

Try 1000 recs and perform a commit at the end of the FORALL statement.
Re: BULK collect performance is slow [message #269834 is a reply to message #269830] Mon, 24 September 2007 15:54 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
6 months is a long to wait for an answer, but heck, maybe the original poster is waiting.
Previous Topic: PL/SQL Question
Next Topic: creating triggers for system generated views
Goto Forum:
  


Current Time: Wed Dec 07 18:32:25 CST 2016

Total time taken to generate the page: 0.05596 seconds