need suggestion on tuning this query

[potluri27]

Hi,

i have a problematic query which originally doesnot finish at all.
then i created query specific projection for "dwh" schema, after which this query takes 83 mins.

i need to further tune this query and need some advises and recommendations.

i am attaching the following :-
1) query
2) explain plan
3) schema export
4) design1_dwh_Deploy
5) design2_dwh_deploy

please help me .

thanks and regards,
Srikanth

[scutter]

In the explain plan that you posted, there are lots of NO STATISTICS. Be sure to update the statistics using analyze_statistics before you run Database Designer. And if you see NO STATISTICS or STALE STATISTICS in any explain plan, update the stats then too.

After updating the stats and doing another query-specific design, if the query still isn't performing well, post another explain plan and the design. We may need to see profiling data too, but start with the explain plan.

--Sharon

[zvika]

Hi,

i have a problematic query which originally doesnot finish at all.
then i created query specific projection for "dwh" schema, after which this query takes 83 mins.

i need to further tune this query and need some advises and recommendations.

i am attaching the following :-
1) query
2) explain plan
3) schema export
4) design1_dwh_Deploy
5) design2_dwh_deploy

please help me .

thanks and regards,
Srikanth

Hi,

As scutter wrote start with analyze all tables.

Then break the query into blocks and test each block time .

You can break it at start with all the union all queries.

Try to get as small query as possible so you will be able to see the part that take the most time and resources .

Then try to see in each query where is the bottleneck and focus on one .

I'm pretty sure that once you will solve one block the solution will be the same for all others blocks.

try to see which part in the explain taking the most time and resource .

use trace to see where it get stuck and focus on that area.

Good luck.