Page 1 of 1
Performance issue in funnel
Posted: Fri Aug 22, 2014 12:27 pm
by dr.murthy
HI ,
I designed with 6 OCI stages and combines data for all 6 stages using funnel and loaded into a final table, how ever this job took 50 mins to complete execution. Actually here out of the six OCI stages initially first 2 stages fetches data renaming stages wait till completes that then next two OCI stages start processing once these completes then next two stages srarts processing data. All these six stages connected to one funnel but these are not fetching data parallel.
Am running this job in two node configuration, Please suggest me how to fetch data from all 6 OCI stages parallely.
Re: Performance issue in funnel
Posted: Fri Aug 22, 2014 3:52 pm
by ray.wurlod
dr.murthy wrote:... but these are not fetching data parallel.
How do you know that?
What style of funnel are you performing?
Posted: Fri Aug 22, 2014 6:39 pm
by qt_ky
Run a test job where you replace the the final table stage with a Copy stage and compare the run times.
Posted: Fri Aug 22, 2014 9:46 pm
by dr.murthy
Yes , I ran that by putting copy stage i could see some improvement in elapsed time but its not fetching data parallel from all the six OCI stages,
By looking at the performance stats i could see OCI stages not fetches data parallel y.
All these six stages we have once common table called TXN_HDR used in join it has 30 million records .
Posted: Sat Aug 23, 2014 7:14 am
by qt_ky
Any chance the 6 select statements have varying complexity in the joins, if any, or the where clauses? It could be that each statement takes a different amount of time to for the database to start returning results.
Posted: Sat Aug 23, 2014 11:08 pm
by dr.murthy
Yes , all the six Sequels has different joins with different tables on TXN table , if all starts picks the records at a same time it can reduce 30 % of run time.
Posted: Sun Aug 24, 2014 5:05 am
by qt_ky
It does not sound like the problem is within the DataStage server. My guess is that the DataStage job may be spending 90% of the time just waiting on the database to provide results from one or more queries, so you need to focus on that aspect.
Run each of the 6 queries separately and see how long each one takes. Then work with another developer and/or DBA to tune the longest running query first, 2nd longest 2nd, optimize each one, make sure they're hitting indexes and not doing full table scans, see if any queries may be combined, etc.
Also have a DBA monitor the database while the normal job run all 6 queries to make sure the database is actually working on each query in parallel and to make sure that enough database server resources are available.