Oracle 8 taking 4 hrs to load !

[vinaymanchinila]

Hi,
I have a simple server job that does no look ups or complex functions, it has 40 columns and mostly they are varchar 10 to varchar 20.
The extraction from a table takes 30 mins but it keeps loading for more than 4 hrs at 100rows/sec.

Is there any thing I can do ?
Thanks,

[dhiraj]

Try increasing the parameter array size and transaction size in your target stage.

Also check if there are indexes on the target table. If so consider dropping them before the load and recreate them after the load completes.

IHTH

Dhiraj

[vinaymanchinila]

Thanks Dhiraj,
Can you let me know how to do this.
There are indexs , how do I drop them and recreate them.

[chulett]

Talk to your DBA first and see if you even have permissions to drop and rebuild them. You'd need scripts to do that for you in the long run, scripts that could be run before and after job or even before and after stage. Some of the considerations for this isn't the number of rows you are loading but rather the number of records in the target table, as index rebuild times can be significant on large tables.

First though, explain what you mean by 'load'. What is your Update action - are these strictly inserts or are there updates involved as well? While dropping indexes can help with straight index loads, that can kill updates.

Increasing the Array Size can definitely help. Transaction Size may or may not and can just complicate your recovery/restart scenarios.

If you are just doing inserts, try this experiment first. Work with your DBA. Have him drop the indexes on the table. Run your job and see if it performs better. Then have your DBA rebuild the indexes. See if the total time - job run plus index drop/rebuilds - is significantly better. If so, only then consider building that functionality into your job.

[vinaymanchinila]

Hi Craig,
The type of load mechanism I am using is "Insert else update" .
Will talk to my DBA and see if the update option again slows it down.
Thanks,

[chulett]

That's what I was afraid of. The "X then Y" update actions are the slowest performing actions you can use, especially if you pick the wrong one. To run the 'else' action, the first action must fail so you are doing double work for those. To me, those two update actions are to be avoided at all costs, other than in very specific applications with very small datasets. But that's just me.

Be aware that, if you drop the indexes, your inserts may never fail so the updates may not happen. You may create duplicates that would keep your indexes from being rebuilt. Without the indexes, your updates could take even longer.

Far better, from a performance standpoint, to take the time to determine which records should be inserts and which should be updates and use two seperate links.

[vinaymanchinila]

Hi Craig,
You mean to say I need to have a hash of the target in the job and see which records are update and the records that dont match are apparently "insert".
Sounds lot better, and I hope the hash will not slow down the process.
Thansk you.

[chulett]

Yup, pretty standard practice. If done properly, the hash will improve performance. Build the hash based on the records to be processed, not everything in the target for starters.

You may even be able to 'kick it up a notch' by only using the OCI stage for the updates and bulk loading (via sqlldr) the inserts.

[anupam]

It's better if you identfy the records need to be inserted and records which needs to be updated. Then u may use sqlldr for Inserting the records with Append mode and can use ORACI stages to update the rest of the records with option update existing records only.

This will definately give u a better throughput.