Oracle stage performance

samsuf2002 · Post by **samsuf2002** » Wed Jan 14, 2009 10:35 am

Hi, My job loads data from Oracle stage into a data set, there are around 6 columns and 200K records. We are doing some inner joins in Oracle stage itself, job takes around 2 hrs to finish on 8 nodes.

My question is what will be the best way to increase the performance of the job ? Is writing a join sql in oracle stage better than using join or look up stages.

Thanks in Advance

DSguru2B · Post by **DSguru2B** » Wed Jan 14, 2009 12:53 pm

Try testing different ways. How much time does the same query take in Toad? IF the fetching of data is not the bottleneck then try playing with the number of nodes, start from 2,4 etc.
What does your design look like, are you re-partitioning the keys before loading the dataset?

samsuf2002 · Post by **samsuf2002** » Wed Jan 14, 2009 2:07 pm

Thanks for your reply DSguru2B.

I will try the steps you showed me and my job design is just an oracle stage followed by data set. I am using auto partitioning on data set.