Writing to ORAOCI9 stage - Performance improvement
Posted: Thu Mar 15, 2007 11:26 am
Hi,
I have a simple job developed by some one else.
ORAOCI9 stage --> Transformer --> ORAOCI9 stage.
I am trying to tune the performance of the job. The job is currently processing 650 rows per second. My source has 10 million rows. At the rate of 650 rps its taking 4 hrs.
We have the same job in Informatica which is processing 6500 rows per sec, 10X faster.
Here are the things I have tried.
The source ORAOCI9 stage had Array size set to 1, I changed it to 32767 (2 to the power 15 -1, as per documentation) and put the @FALSE in the Transformer constraint to see that reading from the DB is not a bottleneck as suggested in one of the posts. Earlier the reading speed used to be 650 rps now after I have changed the Array size and set the constraint in the transformer to @FALSE, the job is reading 6700 rps from source.
Now, I took off the @FALSE in the constraint to allow writing to the target. I had to change the target Array size same as the source as it was also set to 1. Transaction size was set to 0. As my source has 10 million plus rows, I am concerned about running out of buffer and changed the Transaction size to 32767 as per documentaion. There is a Transaction handling tab in the target ORAOCI9 stage which was set to 10,000.
I have changed it to 32767 to make sure it wouldn't become the bottleneck. Now the job is reading just 2100 rows per sec and writing the same.
Any thoughts on how can I improve the performance?
Thank you very much in advance.
I have a simple job developed by some one else.
ORAOCI9 stage --> Transformer --> ORAOCI9 stage.
I am trying to tune the performance of the job. The job is currently processing 650 rows per second. My source has 10 million rows. At the rate of 650 rps its taking 4 hrs.
We have the same job in Informatica which is processing 6500 rows per sec, 10X faster.
Here are the things I have tried.
The source ORAOCI9 stage had Array size set to 1, I changed it to 32767 (2 to the power 15 -1, as per documentation) and put the @FALSE in the Transformer constraint to see that reading from the DB is not a bottleneck as suggested in one of the posts. Earlier the reading speed used to be 650 rps now after I have changed the Array size and set the constraint in the transformer to @FALSE, the job is reading 6700 rps from source.
Now, I took off the @FALSE in the constraint to allow writing to the target. I had to change the target Array size same as the source as it was also set to 1. Transaction size was set to 0. As my source has 10 million plus rows, I am concerned about running out of buffer and changed the Transaction size to 32767 as per documentaion. There is a Transaction handling tab in the target ORAOCI9 stage which was set to 10,000.
I have changed it to 32767 to make sure it wouldn't become the bottleneck. Now the job is reading just 2100 rows per sec and writing the same.
Any thoughts on how can I improve the performance?
Thank you very much in advance.