Oracle : SQL: Sql*Loader utility
If you
want to optimize performance for SQL*Loader here are a few things to
consider (for direct and conventional paths) too Make logical record processing
efficient.
- One-to-one mapping of physical to logical records. Avoid continue if and concatenate.
Field setting is the process of mapping the "fields" in the data file 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.
- 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.
If you can, use the "unrecoverable" option on direct path loads.
3) Even for conventional path loads, always run SQL*Loader directly on the server rather than across a network.
4)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.
5) Disable Indexes and Constraints.
For conventional data loads only, disabling
of indexes and constraints can increase the performance.
6 )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.
7 )Use ROWS=<n> to Commit Less Frequently. For conventional data loads, the rows parameter specifies the number of rows per commit. Issuing fewer commits will increase the performance.
8)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
...
8)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.
- One-to-one mapping of physical to logical records. Avoid continue if and concatenate.
Field setting is the process of mapping the "fields" in the data file 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.
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
(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.
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
2)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.
If you can, use the "unrecoverable" option on direct path loads.
3) Even for conventional path loads, always run SQL*Loader directly on the server rather than across a network.
4)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.
5) Disable Indexes and Constraints.
For conventional data loads only, disabling
of indexes and constraints can increase the performance.
6 )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.
7 )Use ROWS=<n> to Commit Less Frequently. For conventional data loads, the rows parameter specifies the number of rows per commit. Issuing fewer commits will increase the performance.
8)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
...
8)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.
9) Disable Archiving During Load.
While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.
While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.
