Maximizing SQL*Loader Performance
SQL*Loader is
flexible and offers many options that should be considered to
maximize the speed of data loads. These include:
·
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. The fact that SQL is not
being issued makes the entire process much less taxing on the
database. There are certain cases, however, in which direct
path loads cannot be used (clustered tables). 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.
·
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.
·
Use ROWS=n to Commit Less Frequently.
For conventional data loads only, the rows
parameter 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.
$ sqlldr
control=first.ctl parallel=true direct=true
$ sqlldr
control=second.ctl parallel=true direct=true
·
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.
·
Disable Archiving During Load. While
this may not be feasible in certain environments, disabling
database archiving can increase performance considerably.
·
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.
Using the table
table_with_one_million_rows, the following benchmark tests
were performed with the various SQL*Loader options. The table
was truncated after each test.
SQL*Loader Option
|
Elapsed Time (Seconds) |
Time Reduction |
direct=false
rows=64 |
135 |
- |
direct=false
bindsize=512000
rows=10000 |
92 |
32% |
direct=false
bindsize=512000
rows=10000
database in noarchivelog mode |
85 |
37% |
direct=true |
47 |
65%
|
direct=true
unrecoverable |
41
|
70% |
direct=true
unrecoverable
fixed width data |
41 |
70% |
Table 4.3 – Results indicate
conventional path loads take longest.
The results above indicate that
conventional path loads take the longest. However, the
bindsize and
rows parameters can aid the performance under these
loads. The test involving the conventional load didn’t come
close to the performance of the direct path load with the
unrecoverable option
specified.
It is also worth noting that the fastest
import time achieved for this table (earlier) was 67 seconds,
compared to 41 for SQL*Loader direct path – a 39% reduction in
execution time. This proves that SQL*Loader can load the same
data faster than import.
These tests did not compensate for
indexes. All database load operations will execute faster
when indexes are disabled.
To learn more about these techniques,
see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly from the
publisher and get instant access to the code
depot of utilities scripts. |