| Setting LIMIT value while using Bulk Collect [message #648562] |
Thu, 25 February 2016 09:18  |
 |
raj_te
Messages: 46
Registered: August 2013
Location: INDIA
|
Member |
|
|
I wanted to know if we have any technique by which we can calculate the value which needed to be set for a LIMIT clause of bulk collect operation.
for example below lets say our cursor has 10 Million records..What is the value which we can set for LIMIT clause. Is there any way we can calculate it.
decalre
cursor c_emp is <some select query>
var <variable> ;
begin
open c_emp;
loop
fetch c_emp bulk collect into var limit 2;
exit when c_emp%NOTFOUND;
end loop;
close c_emp;
end;
|
|
|
|
|
|
| Re: Setting LIMIT value while using Bulk Collect [message #648567 is a reply to message #648562] |
Thu, 25 February 2016 09:37  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The tests many have done, including me, show that, depending on process memory you can afford, network and work load (including number of concurrent sessions), the optimum is somewhere between 100 and 1000 (this later being the really upper value).
I use and do not exceed 100 which is a good compromise (and is what Oracle also chose to do behind the scene in PL/SQL).
Now you are free to make your own tests and post the results.
[Edit: add Oracle internal PL/SQL value]
[Updated on: Thu, 25 February 2016 09:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Setting LIMIT value while using Bulk Collect [message #648601 is a reply to message #648599] |
Fri, 26 February 2016 03:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's the maximum you can set it to before you crash the process due to lack of memory.
Considering that memory might be needed for other things as well you shouldn't contemplate setting limit anywhere close to that value.
Plus maximum != best for performance.
I'd stick to Michel's advice above.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
