Oracle SQL*Loader (sqlldr) performance tuning

15 pts.
Tags:
Oracle DBA
Oracle performance
Oracle SQL*Loader
Oracle sqlldr
SQL*Loader
Good day, I try to optimize sqlldr, but don't know how to set the parameters. I am trying to load 21,000,000 records per day, but currently don't manage to load all data in time. The loading is always 1 day behind. I am using conventional loading (I cannot disable indexes before and after loading, because of users accessing tables during the day and the time it will take to rebuild indexes). The table contains data for 1 and 1/2 month only with already 800,000,000 records. Each record contains 180 columns. What will the best sizes be to set the following parameters: bindsize, rows, columnarrayrows, readsize. Currently I did not set any parameters. Am using defaults only. Each file to load contains 28,000 records. It took +- 4 minutes to load a file into 4 oracle database tables. Regards Bonita
ASKED: December 9, 2008  6:48 AM
UPDATED: December 11, 2008  12:56 PM

Answer Wiki

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

The bindsize and readsize parameters are related to each other. If you specify a BINDSIZE that is smaller than the size you specified for READSIZE, the BINDSIZE value will be automatically increased to the specified value of READSIZE. If the READSIZE value specified is smaller than the BINDSIZE value, the READSIZE value will be increased.

So, we usually specify only the BINDSIZE parameter.

In general, larger bind arrays improve performance. The documentation says that “In general, you gain large improvements in performance with each increase in the bind array size up to 100 rows. Increasing the bind array size to be greater than 100 rows generally delivers more modest improvements in performance. The size (in bytes) of 100 rows is typically a good value to use.”

We made many tests about this in the past, and in our case, that was true, so we ended up using a bind array of about the size of 100 rows, but this could be different in your case, so I would recommend to make some tests to find what sizes offer better performance.

The ROWS parameters can also be used for this. As part of its initialization, SQL*Loader determines the space required to load a single row, and then multiplies that size by the number of rows specified for the load. <i> “If the number of rows and the maximum bind array size are both specified, SQL*Loader always uses the smaller value for the bind array.”</i>

So, you could start using the ROWS and/or the BINDSIZE parameters only, to see what happens and how load speed varies when varying that parameter.

For example:

<pre>sqlldr user@database control=your_control.ctl rows=100 bindsize=2000000</pre>
(assuming that 2000000 is enough for about 100 rows)

Hope this helps.

Discuss This Question: 2  Replies

 
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
  • carlosdl
    Could you please share your findings and what values you decided to use ? Thanks,
    65,110 pointsBadges:
    report
  • Ws8oBPSQ
    Good day Carlos, thank you very much for the feedback. I did change my sqlldr to be: sqlldr userid=user/password@db control=cdr.ctl log=$logfile discard=$CUR_DIR$oldfilename.discard bad=$CUR_DIR$oldfilename.bad silent=(feedback) errors=10000 bindsize=2500000 readsize=2500000 rows=5000 but still sit with the problem that loading is slow. What I did this morning in desperation is to unable all indexes for the partitions that I still need to load data. This speed up loading to 1 minute instead of 9 minutes. The problem is that queries on the tables are now extremely slow which is a frustration to the users. Any suggestion of other options to try where I don't need to make indexes unusable? Regards Bonita
    15 pointsBadges:
    report

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