Oracle Enterprise Stage::Performance Issue

[binay]

Hello,

I'm trying to read a dataset, do some transformations and finally trying to write to Oracle Stage. The method of writing to the Oracle Stage is Upsert (Insert first and then Update).
The speed I'm currently seeing is approximately 200 rows/sec. When we needed to write more than million records, this takes hours to complete successfully.
Is there any way we can increase the speed and hence performance?

More details about the Oracle Stage:
Target:
Insert SQL: INSERT
INTO
table
(attribute1, attribute2)
VALUES
(stmt_stg_seq.nextval, values)
Insert Array Size = 1
Table = <Table_Name>
Update SQL = UPDATE
Table
SET
Attribute Values
WHERE
1=2
Upsert Order = Insert then Update

[ray.wurlod]

Splitting your stream into two - Insert only and Update only - is the usual recommendation to achieve shortest execution time.

Upsert works by trying one (whichever you have specified as happening first) then, if that fails because of the primary key constraint, trying the other. You can avoid the time wasted in failing if you have two streams.

[binay]

So, you want me take two links from transformers and pass it to two Oracle Stages, one being Insert only and one being Update only? If yes, won't we be walking through the db twice? Any other way to resolve this please?

[ray.wurlod]

I challenge you to find anything more efficient than what I suggested.

[John Smith]

to start with why is your array size set to 1 ? that is very low which means you are commiting every row.try increasing the array size if you can and you will see some improvements.

[sambit]

Is it possible for you to do an update first and then inserts? If your requirement permits this then do it as it will dramatically reduce your execution time. Try it.

[binay]

John - What is the safest size to be defined in the array here and who should be able to help me with that? DBA? Or, it is the developer who needs to decide?

The requirements doesn't permit me to Update first and then Insert.

[John Smith]

try just bumping it up to 100 and see if it helps. it's just a suggestion. you can do that within the job itself.

[binay]

Increased it to 100, but the job aborted.

Error:
<Table Name>: The Orchestrate update field `<Attribute>' is not an Orchestrate insert field;
in order to use the default host array processing for insert, the Orchestrate
fields for update must also be Orchestrate insert fields; this is not a requirement for insert
non-host array processing;
in order to select insert non-host array processing, set the -insertArraySize option to 1.