SQL*Loader (sqlldr) Utility tips

For complete details on using SQL*Loader, see the book "Oracle Utilities".  This is a small excerpt.

Oracle SQL*Loader has dozens of options including direct-path loads, unrecoverable, etc and get super-fast loads. Here are tips for getting high-speed loads with SQL*Loader.

SQL*Loader

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 FOR SCRIPT

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

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.

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 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.

Source: http://www.dba-oracle.com/tips_sqlldr_loader.htm