Re: sqlldr and sort_area_size

"John Leslie" <johnleslie_at_madasafish.com> wrote in message news:4412dd31.0504160039.1fbba5f9_at_posting.google.com...
> What oracle init.ora parameters are relevant to sqlldr direct path
> load performance in Oracle 8.1.7? Is sort_area_size significant?

The degree of importance will be affected by the options that you are using.

sort_area_size can have an impact because if you have indexes on the table you are loading, the indexes have to be brought up to date somehow. The default action in sqlldr direct mode require the loaded data be sorted to produce an indexed set for each index that exists on the table.

Another parameter that could have some effect is the log_buffer - if you are doing large loads, then you may end up with lots of waits for log buffer space: a larger log buffer, up to 10MB or so, my help. This can have an impact on other users if you have concurrent activity going on though.

You may also want to review the size of your log files - if they are small, you may find you are doing frequent log file switches, which can make the load pause every few seconds.

Bear in mind that if the log files are a problem, then the archiver is probably trying to work hard as well, so you may want to do something to optimise your archiver.
.
If you are doing unrecoverable loads, the three points above do not apply.

If these are big loads, you may be able to see where the loading process is losing most time by querying v$session_event for that process.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005