| Good bulk collect limit [message #517016] |
Thu, 21 July 2011 16:17  |
ora1980
Messages: 251
Registered: May 2008
|
Senior Member |
|
|
I wrote a plsql procedure using bulk collect / forall
The procedure uses bulk collect to fetch from a normal cursor, Then I am using for all to insert into
target table, The number of rows are 234965470
Question:
What should ideally be the limit for my bulk collect ?
According to below, it should be in hundreds
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1583402705463
I put a bulk collect limit of 50000 - took close to 2 hours
then i tried 10000 - just 3 mins shorter than the above time
But if you commit every 500 rows, Then is there not another theory that frequent commits is not good ?
Is there something I have to ask the DBAS ?
|
|
|
|
|
|
|
|
| Re: Good bulk collect limit [message #517415 is a reply to message #517020] |
Mon, 25 July 2011 21:50  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
We use BULK COLLECT to cut down on context switching overheads. Say (for example) those overheads represented 5% of total run time (that's a LOT) for a job that does NOT use BULK COLLECT. That's 3 seconds per minute of run time, or 3 minutes per hour.
When we add the BULK COLLECT, we don't expect the SQL to get any faster - it should just reduce the context switching overheads. So say we BULK COLLECT 100 rows at a time. At best, the context switch overhead will reduce by a factor of 100. i.e. 0.03 seconds per minute of run time, or 1.18 seconds per hour.
Say we then change it to BULK COLLECT 50000 rows at a time. Best case is that the overheads are reduced to 0.0036 seconds per hour: a total saving of just over 1 second per hour of processing from BULK COLLECT 100.
As you can see, there is a law of diminishing returns. However there is a risk. The bigger you make your BULK COLLECT array, the more PGA memory you will use. If you run out of PGA, your job will start swapping to disk and will run SLOWER. Considering the minuscule returns from increasing the array size beyond about 100-1000, why would you take the risk?
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
