Slow performance reading from Oracle 10g source
Posted: Fri Feb 12, 2010 8:39 am
Hi !
We have recently moved our source database from oracle 9i to oracle 10g.
One of the datastage server job is designed to read from the source (using custom sql query in OCI stage) and write to a file (no constraints / transformations).
This job runs fine when pointed to 9i source (3200 rows/sec) , but has real slow performance (8 rows/sec) when pointed to 10g source.
We have checked (almost) everything possible in the backend. The table has same number of rows, the indexes are proper, the tables are analyzed, the explain plan generated is the same and the query execution time in SQLPLUS in both the versions is almost same.
But the job has performance issues when pointed to oracle 10g source.
Please do let me know if there are any settings (environment variables etc) to be done in datastage. I have tried playing around with Array size and Prefetch memory settings, without much impact.
Any help would be much appreciated.
Warm Regards
Nagesh
We have recently moved our source database from oracle 9i to oracle 10g.
One of the datastage server job is designed to read from the source (using custom sql query in OCI stage) and write to a file (no constraints / transformations).
This job runs fine when pointed to 9i source (3200 rows/sec) , but has real slow performance (8 rows/sec) when pointed to 10g source.
We have checked (almost) everything possible in the backend. The table has same number of rows, the indexes are proper, the tables are analyzed, the explain plan generated is the same and the query execution time in SQLPLUS in both the versions is almost same.
But the job has performance issues when pointed to oracle 10g source.
Please do let me know if there are any settings (environment variables etc) to be done in datastage. I have tried playing around with Array size and Prefetch memory settings, without much impact.
Any help would be much appreciated.
Warm Regards
Nagesh