Slow performance reading from Oracle 10g source

[srinagesh]

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

[ArndW]

So you can point the same job to a 9i or 10g source and see speed differences; but I believe it much more likely to be because of network bandwidth or database server load rather than due to DataStage. Have you checked this aspect? Also, if you are writing to the DB then it could be because you have different settings, and perhaps even different or missing keys which create the difference in throughput.

[srinagesh]

I have redesigned the job to use OCI8 stage instead of OCI stage. Performance is slightly better (140 rows / sec), but not matching the speed of 9i database.

I have checked with the Network team and they couldn't find any network bottle neck.

-Nagesh

[ArndW]

Write an SQL script that does a bulk load and execute outside of DataStage and compare runtimes. I am pretty confident that they won't run in similar times - again, look for a cause outside of DS first.