- Use Direct Path
Loads - The conventional path loader essentially loads the data by
using standard insert statements. The direct path loader (direct=true)
loads directly into the Oracle data files and creates blocks in Oracle
database block format. To prepare the database for direct path loads,
the script $ORACLE_HOME/rdbms/admin/catldr.sql.sql must be
executed.
- Disable Indexes
and Constraints. For conventional data loads only, the disabling
of indexes and constraints can greatly enhance the performance of
SQL*Loader. The skip_index_maintenance
SQL*Loader parameter allows you to bypass index maintenance when
performing parallel build data loads into Oracle, but only when
using the sqlldr direct=y direct load options.
According to Dave More in his book 'Oracle Utilities' using
skip_index_maintenance=true means 'don't rebuild indexes', and
it will greatly speed-up sqlldr data loads when using parallel
processes with sqlldr:
Also, according to Oracle expert Jonathan Gennick "The
skip_index_maintenance SQL*Loader parameter: 'Controls whether
or not index maintenance is done for a direct path load. This
parameter does not apply to conventional path loads. A value of TRUE
causes index maintenance to be skipped.
-
Use a Larger
Bind Array. For conventional data loads only, larger bind arrays
limit the number of calls to the database and increase performance.
The size of the bind array is specified using the bindsize parameter. The
bind array's size is equivalent to the number of rows it contains (rows=)
times the maximum length of each row.
- Increase the input data buffer -
The
sqlldr readsize parameter determines the input data buffer
size used by SQL*Loader
- Use ROWS=n
to Commit Less Frequently. For conventional data loads only,
rows specifies the number of rows per commit. Issuing fewer
commits will enhance performance.
- Use Parallel
Loads. Available with direct path data loads only, this option
allows multiple SQL*Loader jobs to execute concurrently. Note:
You must be on an SMP server (cpu_count > 2 at least) to
successfully employ parallelism, and you must also employ the append
option, else you may get this error: "SQL*Loader-279:
Only APPEND mode allowed when parallel load specified."
Note that you can also run SQL*Loader in
parallel, and create parallel parallelism:
$ sqlldr
control=first.ctl parallel=true direct=true
$ sqlldr
control=second.ctl parallel=true direct=true
6.
Use Fixed Width
Data. Fixed width data format saves Oracle some processing when
parsing the data.
7.
Disable
Archiving During Load. While this may not be feasible in certain
environments, disabling database archiving can increase performance
considerably.
8.
Use
unrecoverable. The
unrecoverable option (unrecoverable load data) disables the writing of
the data to the redo logs. This option is available for direct path
loads only.
