ORACLE performance

nagarjuna · Post by **nagarjuna** » Wed Jan 04, 2012 11:08 am

Option 1 : Use single oracle enterprise stage & use the single UNION query to get all the data from 52 tables

Option 2: Use 52 oracle entegerprise stages & use a funnel to union the data .

If the you are having a restriction of number of stages , Use union of 5 tables & it will end up in 10 enterpise stages & funnel stage .

Which option gives better performance depends on the load on the datastage & database servers.

qt_ky · Post by **qt_ky** » Wed Jan 04, 2012 12:20 pm

Option 2 sounded like using a single multi-instance job rather than 52 database stages in one job. Is that correct?

Note: Oracle Connector stages are recommended over Oracle Enterprise stages.

I would not know which option performs better without testing it both ways.

pandeesh · Post by **pandeesh** » Wed Jan 04, 2012 1:16 pm

For me option1 looks good.

dsusersaj · Post by **dsusersaj** » Wed Jan 04, 2012 1:25 pm

From my experience, if the table joins take more time while running as a single query with joins(usually happens when the tables are not indexed or if the join is not on key fields), datastage joins worked faster.

So I would recommend option 2.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 04, 2012 3:02 pm

Are these, or some of them, materialized views?

How complex are the constraints in the query? How complex are the join criteria?

sukisgacn · Post by **sukisgacn** » Thu Jan 05, 2012 5:24 am

We are not planning to use 52 stages rather , we are using 52 instances of the same job running parallely using datastage sequence. Each job will have a single query. The queries are not complex -just simple select * statements where Dateattribute between <start_date> and <end date>.
However the data volume is large - upto 15 million records per SQL. And requirement is to process as fast as possible.

None of these are materialised view. Its a Oracle Federated database on 52 different databases.

Problem with running a single SQL and union is Oracle union runs each SQL sequentially.

Does any of you see any issues in using 52 instances of the same job running parallelly?

nagarjuna · Post by **nagarjuna** » Thu Jan 05, 2012 7:07 am

I think Running 52 instances of same job parallely is a too much load on the server ...I think it's not a bad idea to have 10 connector stages & union 5 tables in each connector stage ..Also there used to be a limitation of number of instances of job running in parallel in 8.0.1 ..Not so sure if it fixed in the later versions ..

qt_ky · Post by **qt_ky** » Thu Jan 05, 2012 6:09 pm

Has your Oracle DBA already talked with Oracle support about optimizing the database to run those queries in parallel? It would seem if you can run the queries in parallel using DataStage that you could do the same directly within Oracle. (I wonder if the DB2 Federation Server has any similar limitation.)

I'm running over 50 multi-instance jobs in parallel now on 8.5, but they are not selecting large volumes of data. It should depend on your DataStage server's hardware resources and software and OS configuration.

If you go the DataStage route and hit a resource limit, then you could use a sequence job to run the 52 instances in 2 to 4 groups at a time back to back (15 to 25 jobs at a time).