Sql loader: Control Commit Interval

Analyzing the ADDM report we have found below:

Wait class "Commit" was consuming significant database time

It was a DB where an application continuously loads data using sql loader (using default parameters). As a result, huge amount of commits are always going on.

Now we want to control the number of rows between commits.

We changed the sqlldr script by appending ROWS=1000 and found below on sql loader log:

Space allocated for bind array: 495360 bytes(40 rows)

So, commit is occurring after insertion of each 40 rows which uses BIND size of 495360 bytes.

Now we have to calculate the appropriate size for BIND. We calculated as [(495360/40)*1000] bytes.

So, we have to use a new parameter BINDSIZE and append below in the old sqlldr command:

BINDSIZE =12384000 rows=1000

But we have to control BINDSIZE so that it doesn't exceed the max permitted value for READSIZE (read buffer size) for the platform (for 9i database it is 20MB or 20971520 bytes)[http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm#1004855]

Now our sql loader logfile show as below:

Space allocated for bind array: 12384000 bytes(1000 rows)

Read buffer bytes:12384000

So, we are getting exactly 1000 rows interval for commits.

[Thanks to ssunda6: http://www.orafaq.com/forum/t/84903/2/]