SQL*Loader
()
is
the
utility
to
use
for
high
performance
data
loads.
The
data
can
be
loaded
from
any
text
file
and
inserted
into
the
database.
SQL*Loader
reads
a
data
file
and
a
description
of
the
data
which
is
defined
in
the
control
file.
Using
this
information
and
any
additional
specified
parameters
(either
on
the
command
line
or
in
the
PARFILE),
SQL*Loader
loads
the
data
into
the
database.
During
processing,
SQL*Loader
writes
messages
to
the
log
file,
bad
rows
to
the
bad
file,
and
discarded
rows
to
the
discard
file.
The
sqlldr
Control
File
The
SQL*Loader
control
file
contains
information
that
describes
how
the
data
will
be
loaded.
The
sqlldr
file
contains
the
table
name,
column
datatypes,
field
delimiters,
etc.
and
provides
the
guts
for
all
SQL*Loader
processing.
Manually
creating
control
files
is
an
error-prone
process.
The
following
SQL
script
()
can
be
used
to
generate
an
accurate
control
file
for
a
given
table.
The
script
accepts
a
table
name
and
a
date
format
(to
be
used
for
date
columns),
and
generates
a
valid
control
file
to
use
with
SQL*Loader
for
that
table.
SEE
CODE
DEPOT
Once
executed
and
given
a
table
name
and
date
format,
controlfile.sql
will
generate
a
control
file
with
the
following
contents:
SEE
CODE
DEPOT
The
control
file
can
also
specify
that
records
are
in
fixed
format.
A
file
is
in
fixed
record
format
when
all
records
in a
datafile
are
the
same
length.
The
control
file
specifies
the
specific
starting
and
ending
byte
location
of
each
field.
This
format
is
harder
to
create
and
less
flexible
but
can
yield
performance
benefits.
A
control
file
specifying
a
fixed
format
for
the
same
table
could
look
like
the
following:
LOAD
DATA
INFILE
'table_with_one_million_rows.dat'
INTO
TABLE
TABLE_WITH_ONE_MILLION_ROWS
SEE
CODE
DEPOT
The SQL Loader Log
File
The log file contains
information about the
SQL*loader execution. It
should be viewed after
each SQL*Loader job is
complete. Especially
interesting is the
summary information at
the bottom of the log,
including CPU time and
elapsed time. The data
below is a sample of the
contents of the log
file.
SQL*Loader is flexible and
offers many options that should be considered to maximize the speed of
data loads. These include many permutations of the SQL*Loader
control file parameters:
OPTIONS (DIRECT=TRUE, ERRORS=50, rows=500000)
UNRECOVERABLE LOAD DATA
- 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.
See complete sqlldr directions
here:
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
