improve performance of OCI stage
Moderators: chulett, rschirm, roy
improve performance of OCI stage
Hi,
we have a job where we pull data from a oracle table and then we have one stage which inserts data without clearing and another OCI to update existing data.
But the data is very huge around 30 million records.the record length is around 40 characters,right now i have array size set to 5000 and transaction size also is set to 5000.But job is very slow at 1400 rows/sec.
Can some one through some light on how to improve the performance of this job.
Thanks
sheema
we have a job where we pull data from a oracle table and then we have one stage which inserts data without clearing and another OCI to update existing data.
But the data is very huge around 30 million records.the record length is around 40 characters,right now i have array size set to 5000 and transaction size also is set to 5000.But job is very slow at 1400 rows/sec.
Can some one through some light on how to improve the performance of this job.
Thanks
sheema
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Re: improve performance of OCI stage
So, you are using the OCI stage to run over 30 million dynamically created SQL statements that must all be parsed and executed on your Oracle database? Can you just produce a flat file and run sqlldr with replace option or bulk load your data to a dummy table that you can run the Merge command against?sheema wrote:Hi,
we have a job where we pull data from a oracle table and then we have one stage which inserts data without clearing and another OCI to update existing data.
But the data is very huge around 30 million records.the record length is around 40 characters,right now i have array size set to 5000 and transaction size also is set to 5000.But job is very slow at 1400 rows/sec.
Can some one through some light on how to improve the performance of this job.
Thanks
sheema
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What I have done in the past is seperate out inserts and updates. This is more of a performance tuning issue and how to handle large amount of data in already populated table which should handle insert and updates.
First I separate inserts and bulk load them to target and then via another job run the updates. If performance is still not acceptable, I update the table first and then if there are indexes/constraints that can be disabled/dropped, I disable/drop them. Then insert all rows via bulk load and then enable constraint with no validate option.
Unfortunately, in most of the case there is no standard way of speeding up the process because each table is different with different constraint and indexes etc.
First I separate inserts and bulk load them to target and then via another job run the updates. If performance is still not acceptable, I update the table first and then if there are indexes/constraints that can be disabled/dropped, I disable/drop them. Then insert all rows via bulk load and then enable constraint with no validate option.
Unfortunately, in most of the case there is no standard way of speeding up the process because each table is different with different constraint and indexes etc.
Datawarehouse Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I have split up my inserts and updates into 2 jobs
My Job design is as below
OracleEnterprise1----->Trnsfr------->Trnsfr1------->Oracle Enterprise2
In the stage OracleEnterprise1 i extract data from a oracle table, In Trnsfr I do Trim 3 or 4 columns and add a couple of new columns then map them to Trnsfr1 and load into the Oracle table using Oracle Enterprise2 stage(in job1) and update into oracle(in job2).
The no of updates are more than the inserts into the table.
Is there any way i can speed up the process.
Thanks
My Job design is as below
OracleEnterprise1----->Trnsfr------->Trnsfr1------->Oracle Enterprise2
In the stage OracleEnterprise1 i extract data from a oracle table, In Trnsfr I do Trim 3 or 4 columns and add a couple of new columns then map them to Trnsfr1 and load into the Oracle table using Oracle Enterprise2 stage(in job1) and update into oracle(in job2).
The no of updates are more than the inserts into the table.
Is there any way i can speed up the process.
Thanks
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Truncate and Bulk load a table with similar schema and then use the Oracle MERGE command to populate the production table. When the rows are "WHEN MATCHED THEN" on the primary key, update the existing rows and "WHEN NOT MATCHED THEN" insert into the production table.sheema wrote:I have split up my inserts and updates into 2 jobs
My Job design is as below
OracleEnterprise1----->Trnsfr------->Trnsfr1------->Oracle Enterprise2
In the stage OracleEnterprise1 i extract data from a oracle table, In Trnsfr I do Trim 3 or 4 columns and add a couple of new columns then map them to Trnsfr1 and load into the Oracle table using Oracle Enterprise2 stage(in job1) and update into oracle(in job2).
The no of updates are more than the inserts into the table.
Is there any way i can speed up the process.
Thanks
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
Define manually? For instance, you could write your own shell script and use the external source stage to invoke the MERGE command and capture the return code and feed the return code back to datastage and any results output to standard output can be sent down a link and into a sequential file. You could use a single varcharacter column to capture the results and no delimeter. Then, you can still invoke this job through a datastage sequencer if that is a must.sheema wrote:Thanks,
How is the Merge command supposed to run. Will it be run manually by DBA to load the table.
Thanks