Total Pageviews

October 1, 2016

10/01/2016 12:02:00 PM
Oracle Sql* Loader Performance Issues
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 continueif 
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. 


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

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


o If you can, use the "unrecoverable" option on direct path loads.

o Even for conventional path loads, always run SQL*Loader directly on the
server rather than across a network.
o 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.

o Disable Indexes and Constraints. For conventional data loads only, disabling 
of indexes and constraints can increase the performance.


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


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

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

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


o Disable Archiving During Load. While this may not be feasible in certain 
environments, disabling database archiving can increase performance 
considerably.
 
Related Posts Plugin for WordPress, Blogger...