SQL loader

260 pts.
Tags:
Oracle 11g
Oracle Database
Oracle Database 11g
SQL Loader
Like to hear best practices to be followed to increase performance of SQL loader.



Software/Hardware used:
Oracle Datbase 11G
ASKED: August 25, 2009  6:16 AM
UPDATED: August 25, 2009  2:07 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

These are some of the things you can do to increase load performance:

- Use direct path load when possible. Conventional path loads execute INSERT statements for each row, while directh path loads write data blocks directly to the files, and it is much faster.

- In conventional path loads use an appropriate value for the BIND ARRAY SIZE parameter. Using larger values usually improves performance. The best size will depend on the size of the rows.

- In conventional path loads use an appropriate value for the ROWS parameter. It defines how many rows will be inserted before a commit is issued. Using larger values usually improves performance, but as you increase the number of rows, there is a point where larger values don’t make any noticeable difference. Some tests could be needed to determine the best value.

- Disabling constraints and indexes when using conventional path loads will improve performance as well.

- If possible, set the PARALLEL parameter to true in direct path loads, to use more than one job to load data simultaneously.

Here’s the SQL*Loader documentation for reference:

<a href=”http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14215/ldr_params.htm”>SQL*Loader Command-Line Reference</a>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following