Home » SQL & PL/SQL » SQL & PL/SQL » BUlk insert limit with data blocks
BUlk insert limit with data blocks [message #383365] Wed, 28 January 2009 09:12 Go to next message
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

How to estimate the limit number when doing bulk insert? I heard it can be estimated based on table data block.

is it true?
if so, how to estimate it?

Re: BUlk insert limit with data blocks [message #383374 is a reply to message #383365] Wed, 28 January 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65089
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Total size is about "bulk limit" * "row size".
Only YOU know what should be the acceptable limit given the server memory and the number of concurrent sessions you have.

Re: BUlk insert limit with data blocks [message #383379 is a reply to message #383365] Wed, 28 January 2009 10:36 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's a diminishing return. - You are making a saving due to reduced context switching, but you still have the processing time of the actual SQL.
So if you go from a limit of 1 to a limit of 100, you're going from 100 context switches + 100 sql executions to 1 context switch and 100 sql executions - saving the time for 99 block switches per 100 rows.

If you increase the limit by a factor of 10 to 1000, you only save 0.9 block switches per 100 rows - not much really.

I've usually found that a limit of 100 -1000 improves performance as much as you can notice.
Previous Topic: creating User & assign Role
Next Topic: Insert and creation of archive redo logs
Goto Forum:

Current Time: Thu Jul 27 03:47:27 CDT 2017

Total time taken to generate the page: 0.08363 seconds