Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

SQL*Loader (sqlldr) Utility tips

Oracle Tips by Burleson Consulting

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.

Bad file and discard files

See bad file and discard 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 unrecoverableThe unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs.  This option is available for direct path loads only.

Related SQL*Loader Articles:

Maximizing SQL*Loader Performance
Hypercharge SQL*Loader load speed performance
Loading large datasets with SQL*Loader
See complete sqlldr directions here:

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 


 

 

  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.