These are some of the things you can do to increase load performance:
- Use direct path load when possible. Conventional path loads execute INSERT statements for each row, while directh path loads write data blocks directly to the files, and it is much faster.
- In conventional path loads use an appropriate value for the BIND ARRAY SIZE parameter. Using larger values usually improves performance. The best size will depend on the size of the rows.
- In conventional path loads use an appropriate value for the ROWS parameter. It defines how many rows will be inserted before a commit is issued. Using larger values usually improves performance, but as you increase the number of rows, there is a point where larger values don’t make any noticeable difference. Some tests could be needed to determine the best value.
- Disabling constraints and indexes when using conventional path loads will improve performance as well.
- If possible, set the PARALLEL parameter to true in direct path loads, to use more than one job to load data simultaneously.
Here’s the SQL*Loader documentation for reference:
<a href=”http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14215/ldr_params.htm”>SQL*Loader Command-Line Reference</a>