If you want to optimize performance for SQL*Loader here are a few things
to consider (for direct and conventional paths):
o Make logical record processing efficient.
- One-to-one mapping of physical to logical records. Avoid continueif
and concatenate.
o Make field setting efficient.
Field setting is the process of mapping the "fields" in the datafile
to their corresponding columns in the database. The mapping function
is controlled by the description of the fields in the control file.
Field setting is the biggest consumer of CPU time for most loads.
- Avoid delimited fields; use positional fields. If you use
delimited fields, SQL*Loader has to scan the input data looking
for the delimiter(s)/enclosure(s). If you use positional fields,
SQL*Loader just increments a pointer to get to the next field
(very fast).
- If you are using positional fields, avoid trimming white space.
That is, use PRESERVE BLANKS.
Note that a common theme in points 1 and 2 above is to avoid scanning
the input data.
o Make conversions efficient.
There are several conversions that SQL*Loader does for you;
character set conversions and datatype conversions.
- Avoid character set conversions if you can. SQL*Loader supports
three character sets:
a) Client character set (NLS_LANG of the sqlldr process.)
b) Server character set.
c) Datafile character set.
Performance is optimized if all three are the same, most importantly
b) and c). Also, memory for character set conversion buffers is not
allocated if these are the same.
- Avoid multi-byte character sets if you can.
- As for datatype conversions (SQL*Loader datatype to database column
datatype), char to char is efficient if the same character set is in
use for the datafile and the server. That is, no conversion is fast.
Therefore, try to minimize the number of conversions that you have
to do.
o If you can, use the "unrecoverable" option on direct path loads.
o Even for conventional path loads, always run SQL*Loader directly on the
server rather than across a network.
o Reduce non-database file I/O
- If possible, use SILENT=ERRORS so error messages are not written to the log. This is useful if loading known duplicates.
- Also, use BAD=/dev/nul (UNIX) or BAD=NUL (DOS) so bad records do not generate I/O.
o Disable Indexes and Constraints. For conventional data loads only, disabling
of indexes and constraints can increase the performance.
o Use a Larger Bind Array. For conventional data loads, 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.
o Use ROWS=
the rows parameter specifies the number of rows per commit. Issuing
fewer commits will increase the performance.
o Use Parallel Loads. Available with direct path data loads, this option
allows multiple SQL*Loader jobs to execute concurrently.
$ sqlldr control=first.ctl parallel=true direct=true
$ sqlldr control=second.ctl parallel=true direct=true
...
o Use Fixed Width Data. Fixed width data format saves Oracle some processing
when parsing the data. The savings can be tremendous, depending on the
type of data and number of rows.
o Disable Archiving During Load. While this may not be feasible in certain
environments, disabling database archiving can increase performance
considerably.
