Oracle Enterprise Stage::Performance Issue
Moderators: chulett, rschirm, roy
Oracle Enterprise Stage::Performance Issue
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 193
- Joined: Tue Sep 05, 2006 8:01 pm
- Location: Australia
-
- Charter Member
- Posts: 193
- Joined: Tue Sep 05, 2006 8:01 pm
- Location: Australia
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.
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.