Oracle OCI stage reading...
Moderators: chulett, rschirm, roy
Oracle OCI stage reading...
Hi all,
Is there any positive output for reading oracle database table from OCI9I stage in server job by changing Array Size and Pre fetch memory settings,
if yes then what should be that?
reply me Thanks.
Is there any positive output for reading oracle database table from OCI9I stage in server job by changing Array Size and Pre fetch memory settings,
if yes then what should be that?
reply me Thanks.
To demonstrate that DataStage is not your problem, put @FALSE in a Transformer stage output constraint immediately after the OCI stage. I'm fairly sure your query is not sending rows very quickly to DataStage.
Also, make sure you're looking at the database server and DataStage server cpu utilization (prstat, topas, top, glance, etc) to insure that your expectation of performance is in alignment with server resources.
Also, make sure you're looking at the database server and DataStage server cpu utilization (prstat, topas, top, glance, etc) to insure that your expectation of performance is in alignment with server resources.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Ken,Can you explain me more about it ,i did not get your point and also show me how can i do.?kcbland wrote:To demonstrate that DataStage is not your problem, put @FALSE in a Transformer stage output constraint immediately after the OCI stage. I'm fairly sure your query is not sending rows very quickly to DataStage.
Also, make sure you're looking at the database server and DataStage server cpu utilization (prstat, topas, top, glance, etc) to insure that your expectation of performance is in alignment with server resources.
Have a stream coming out of OCI stage into the transformer and then from there to any flat file. Provide the constraint inside the transformer as @FALSE so that no rows get written to the flat file. Run your job. This way you will find out where is the bottleneck in the performance. The extract rows/sec should be able to tell you that. If its the same, then that means its network issue and your network is heavily loaded.
To confirm that, use one of the utilities provided by kcbland.
To confirm that, use one of the utilities provided by kcbland.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
No need to remove the target OCI stage. Just do as Ken suggested and change your constraint(s) to @FALSE - literally. That will shut down the target half of your job and you can see how quickly your current settings can pull data out of your database.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Why is it surprising? The DataStage job can get the data from the database as fast as it can. However, what it does with it afterwards has consequences.
I suspect you have a lot of logic after the Transformer stage that slows down the receiving of the data. This is why we ALWAYS recommend simpler jobs.
OCI --> XFM --> SEQ
SEQ --> XFM --> HASH
SEQ -->XFMs/HASH lookups --> SEQ or HASH
SEQ --> XFM --> OCI
Each style of job will run fast because you're not mixing database operations with transformation operations. Both slow each other down more when in the same job than when in separate jobs.
I suspect you have a lot of logic after the Transformer stage that slows down the receiving of the data. This is why we ALWAYS recommend simpler jobs.
OCI --> XFM --> SEQ
SEQ --> XFM --> HASH
SEQ -->XFMs/HASH lookups --> SEQ or HASH
SEQ --> XFM --> OCI
Each style of job will run fast because you're not mixing database operations with transformation operations. Both slow each other down more when in the same job than when in separate jobs.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Look at the cpu utilization of your job. This will indicate if it's working as hard as it can. How about posting a "ls -lR" of the hashed files?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle