Performance problem

[mousazzi]

Hi all. I have to load a table with a file of 3.5 Gb corrisponding to 9.000.000 record. Initially DataStage loads record at 1.500 rows/second but, after 2.000.000 record, it decrease to 500 r/s ad finish at 250 r/s. Is there an answer to this behaviour ? In which way can i increase the performace ? Thanks in advance.

[senthilmp]

I think you can use ORACEL BULK LOAD stage, this will give you enormous amount of performance tuning

[Saad]

Can you give some more information about your job structure, like it source stages, intermediate processing stages and finally where actually you're inserting/writing data ?

Saad

[mousazzi]

The job is:
Oci Stage -> Transformer -> Oci Stage
The job perform an Update / Insert record
Hope this is useful

[senthilmp]

if its doing update and insert mean it will take more time to process, Oracle bulk load doesnt support this.

[vijay.barani]

We can use DRS STAGE instead of oracle bulk load stage

[chulett]

The DRS stage set to 'Oracle' is functionally equivalent to the OCI stage, so there's no gain from switching. And it doesn't take the place of a bulk loader any more than OCI does.

[chulett]

if its doing update and insert mean it will take more time to process, Oracle bulk load doesnt support this.

It does, to some extent, with a conventional (DIRECT=FALSE) load. But you're correct about the performance over the duration of the load. Any of those 'combo' Update Actions are a crutch, IMHO, that enable poor or lazy job design, and picking the correct one is critical to minimizing the damage.

Think about it. If you say 'Insert else Update' and what you end up doing are primarily updates, then you are burning two database operations to get one record into the table. Same goes for the opposite situation. Some portion of your loads will take twice as long as they should. Plus there's the old standby of updating with non-indexed Key coumns, each update is a full table scan so the more you do the slower it can get. If both are going on - double whammy.

Best solution? Pre-qualify your inserts and updates using a reference hashed file, the Heart & Soul of the Server product. Bulk load the inserts with a direct path load. Then the updates can follow, either as normal OCI updates or perhaps as a conventional bulk load or even as a MERGE performed from a bulk-loaded work table. This becomes a teensy bit more complicated if records inside the dataset you are loading fall into both the insert and update camp, but it's not that hard to handle.

Bottom line, anything noted above - heck, even pre-qualified OCI insert and updates links (plural) - will perform better than your current design.