SQL LOADER PERFORMANCE - Using a larger bind array

The bind array is the area in memory where SQL*Loader stores a batch of data to be loaded. When the bind array fills, it is transmitted to the database. Generally, the larger the bind array, the more rows can be loaded in one operation, and the better your performance will be. The bind array size is controlled by the READSIZE and BINDSIZE parameters, and the default size is 64KB (256KB in Oracle9i). Finding the optimal setting for a given load takes a bit of trial and error.

First, increase the READSIZE and BINDSIZE settings (usually, both should be the same). Then, determine the number of rows that will fit within the bind array, and set the ROWS parameter to that value. Run the load. If you see a performance gain, try increasing the bind array size once again. Continue this process until you are comfortable with the performance.

ROWS – BINDSIZE – READSIZE – How it Works?

When we are using both ROWS and BINDSIZE options, first sqlldr calculates the space(in bytes) a row can take (based on the column datatypes and the number of columns of the table). And then multiplies that size by the number of ROWS. If that size is less than the BINDSIZE(bind array size) mentioned, it is taking the value of ROWS as commit interval.
If the size of the number of ROWS is large that it does not fit in the BINDSIZE value specified, then it uses small number of rows that can fit into the BINDSIZE and then commits at that interval.
BINDSIZE value should match with the READSIZE value. If we give very large value for BINDSIZE that exceeds the maximum limit set for READSIZE , it throws an error. If the BINDSIZE value does not exceed max value of Read buffer size(set on the platform), then the Read buffer size is automatically increased to the size of Bind array and it reads that many number of bytes from datafile.
The READSIZE parameter is used only when reading data from datafiles. When reading records from a control file, a value of 64 kilobytes (KB) is always used as the READSIZE.
The READSIZE parameter lets you specify (in bytes) the size of the read buffer, if you choose not to use the default. The maximum size allowed is 20 megabytes (MB) for both direct path loads and conventional path loads.