SQL*Loader slower and slower, but.. (URGENT)

[cgswong]

My SQL*Loader use to take 5 minutes to load 70,000 records, and then overnight it started taking 5 hours (went from 2 to 5 in two days) to load just 20,000. I split the records in batches of 50,000 which took 13 minutes per batch, but then this started to take 3 hours and now the whole day!

However, if I load multiple batches (50,000 records) at the same time (like 4 SQL*Loaders running) I can get back the 13 minutes load time.

Why is this happening and how do I solve this please? I'd really appreciate any help as the shareholders are taking about jobs be lost and it's my job evaluation period now.

Thanks.

 

[karluk]

I don't know if it's the cause of your problem, but I see Metalink document 1026145.6, which suggests that slow loads may be due to SQL*Net tracing being turned on. Check the value of parameter TRACE_LEVEL_CLIENT in your SQL*Net.ORA file.

 

[sem]

Try to investigate whether other batch jobs are executed nightly at the same time. They may both spend a lot of resources or lock some objects.

 

[neilsie]

Wongyc2k,
there are a few things that you may first wish to check regarding your usages:

1) Use direct, non-recoverable load
2) Load from a file locate local to the server - ie not across your network
3) Consider the frequency of your commits, and/or if this a the only batch job, switch off all small rbs and online for the duration of this job, one very large one.
4) Use fixed positional input data and not delimiters - otherwise each record has to be then scanned per field
5) Consider telling sqlloader the length of a record - doesn't have to scan for line feeds eg infile input.dat "fix 100" (mandatory quotes) (only works if each record is off a fixed length - eg type raw.
6) if you have to trim white space - do so on the generating system (depending on how long that takes)
7) Ensure your chr sets are the same ie datafile and server.
8) depending on your schema, consider removing relevant indexes and rebuilding upon finish. Also examine the clause "sorted indexes" ie pre-sort your data.

hope this gives you some initial pointers..

br,
Neil.