The number of column array rows determines the number of rows loaded before the stream buffer is built. The
STREAMSIZE parameter specifies the size (in bytes) of the data stream sent from the client to the server.
COLUMNARRAYROWS parameter to specify a value for the number of column array rows. Note that when
VARRAYs are loaded using direct path, the
COLUMNARRAYROWS parameter defaults to 100 to avoid client object cache thrashing.
The optimal values for these parameters vary, depending on the system, input data types, and Oracle column data types used. When you are using optimal values for your particular configuration, the elapsed time in the SQL*Loader log file should go down.
You should monitor process paging activity, because if paging becomes excessive, then performance can be significantly degraded. You may need to lower the values for
COLUMNARRAYROWS to avoid excessive paging.
It can be particularly useful to specify the number of column array rows and size of the steam buffer when you perform direct path loads on multiple-CPU systems.