If you are performing direct path loads on a multiple-CPU system, then SQL*Loader uses multithreading by default. A multiple-CPU system in this case is defined as a single system that has two or more CPUs.
Multithreaded loading means that, when possible, conversion of the column arrays to stream buffers and stream buffer loading are performed in parallel. This optimization works best when:
Column arrays are large enough to generate multiple direct path stream buffers for loads
Data conversions are required from input field data types to Oracle column data types
The conversions are performed in parallel with stream buffer loading.
The status of this process is recorded in the SQL*Loader log file, as shown in the following sample portion of a log:
Total stream buffers loaded by SQL*Loader main thread: 47 Total stream buffers loaded by SQL*Loader load thread: 180 Column array rows: 1000 Stream buffer bytes: 256000
In this example, the SQL*Loader load thread has offloaded the SQL*Loader main thread, allowing the main thread to build the next stream buffer while the load thread loads the current stream on the server.
The goal is to have the load thread perform as many stream buffer loads as possible. This can be accomplished by increasing the number of column array rows, decreasing the stream buffer size, or both. You can monitor the elapsed time in the SQL*Loader log file to determine whether your changes are having the desired effect. See "Specifying the Number of Column Array Rows and Size of Stream Buffers" for more information.
On single-CPU systems, optimization is turned off by default. When the server is on another system, performance may improve if you manually turn on multithreading.
To turn the multithreading option on or off, use the MULTITHREADING parameter at the SQL*Loader command line or specify it in your SQL*Loader control file.
Oracle Call Interface Programmer's Guide for more information about the concepts of direct path loading