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.
Use the COLUMNARRAYROWS
parameter to specify a value for the number of column array rows. Note that when VARRAY
s are loaded using direct path, the COLUMNARRAYROWS
parameter defaults to 100 to avoid client object cache thrashing.
Use the STREAMSIZE
parameter to specify the size for direct path stream buffers.
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 READSIZE
, STREAMSIZE
, and 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.