11 Replies Latest reply on Apr 18, 2014 7:18 PM by jihuyao

How i can rewrite the below query

user590978 Apr 15, 2014 1:56 PM

Hi,

This is DW query and taking long time to run..Can you please help me to rewrite the query in other way to increase the performance.

{code}

SELECT /*+ parallel(AL2,4) */

AL2.DM_PRODUCT_HKEY,

AL2.DM_WEEK_END_DATE_DKEY,

NVL(AL1.DM_SEGMENTATION_DKEY,-100) AS DM_SEGMENTATION_DKEY,

NVL(AL1.DM_TRAVELER_STATUS_DKEY,-100) AS DM_TRAVELER_STATUS_DKEY,

NVL(AL1.DM_TRAV_ON_SEGM_DKEY,8) AS DM_TRAV_ON_SEGM_DKEY,

SUM ((NVL(AL1.HH_COUNT,1)/NVL(AL1.TOTAL_HH_COUNT,1))*AL2.TIMED_TOTAL_ADV_COST ) AS ADVG_COST_ACTUALS,

AL3.PRODUCT_YEAR AS YEAR

FROM ADVT_WRK_TBL AL2

INNER JOIN MKTRPT_VW AL3

ON (AL2.DM_PRODUCT_HKEY=AL3.DM_PRODUCT_HKEY)

INNER JOIN HH_COUNT_FACT AL1

ON (AL2.DM_PROMOTIONS_DKEY=AL1.DM_PROMOTIONS_DKEY

AND AL3.PRODUCT_YEAR=AL1.YEAR )

GROUP BY

AL2.DM_PRODUCT_HKEY,

AL3.PRODUCT_YEAR,

NVL(AL1.DM_SEGMENTATION_DKEY,-100),

NVL(AL1.DM_TRAVELER_STATUS_DKEY,-100),

NVL(AL1.DM_TRAV_ON_SEGM_DKEY,8),

AL2.DM_WEEK_END_DATE_DKEY

{code}

This is view i am USING MKTRPT_VW for one of the joining above.

Thanks in advance!..

1. Re: How i can rewrite the below query

jihuyao Apr 15, 2014 3:52 PM (in response to user590978)

Not sure how to rewrite this without any info on the view. But take chance in advance and maybe a different join order, AL3 - AL2 - AL1 makes the execution plan less complicated.
Like Show 0 Likes(0)

Actions
2. Re: How i can rewrite the below query

user590978 Apr 15, 2014 5:28 PM (in response to jihuyao)

I tried your suggestion but it does not help.

From view i am only getting three columns. below is view query. its view because of data refresh.
{code}

SELECT PH.DM_PRODUCT_HKEY,
          PH.PRODUCT_YEAR,
          CPH.BASE_CODE_SUMMARY
     FROM PRODUCT_H PH
          INNER JOIN
          CURT_PRO_HIE CPH
             ON (    PH.HIERARCHY_LEVEL = CPH.HIERARCHY_LEVEL
                 AND PH.HIERARCHY_LEVEL_ITEM = CPH.HIERARCHY_LEVEL_ITEM);
{code}

Please suggest.

Thanks,
Like Show 0 Likes(0)

Actions
3. Re: How i can rewrite the below query

SGUN Apr 15, 2014 5:45 PM (in response to user590978)

Check if you have indexes on the columns? It would be useful if you provide some sample data.
How big is your each table? How many rows are you hitting by joining?
Like Show 0 Likes(0)

Actions
4. Re: How i can rewrite the below query

user590978 Apr 15, 2014 6:04 PM (in response to SGUN)

Below is the explain plan for view query..:

{code}
Plan
SELECT STATEMENT ALL_ROWSCost: 396 Bytes: 1,060,226 Cardinality: 22,558
23 PX COORDINATOR
22 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10004 :Q1004Cost: 396 Bytes: 1,060,226 Cardinality: 22,558
21 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 396 Bytes: 1,060,226 Cardinality: 22,558
8 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1004
7 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 50 Bytes: 90,048 Cardinality: 4,288
6 PX SEND HASH PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 50 Bytes: 90,048 Cardinality: 4,288
5 VIEW VIEW DM.index$_join$_002 Cost: 50 Bytes: 90,048 Cardinality: 4,288
4 HASH JOIN
1 INDEX FAST FULL SCAN INDEX (UNIQUE) DM.DM_CURRENT_PRODUCT_HIERARCHYPK Cost: 18 Bytes: 90,048 Cardinality: 4,288
3 BITMAP CONVERSION TO ROWIDS Cost: 34 Bytes: 90,048 Cardinality: 4,288
2 BITMAP INDEX FULL SCAN INDEX (BITMAP) DM.IDX_NK_CURR_PROD_HIERARCHY
20 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 344 Bytes: 1,325,480 Cardinality: 50,980
19 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003Cost: 344 Bytes: 1,325,480 Cardinality: 50,980
18 VIEW VIEW PARALLEL_COMBINED_WITH_PARENT DM.index$_join$_001 :Q1003Cost: 344 Bytes: 1,325,480 Cardinality: 50,980
17 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1003
12 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 138 Bytes: 1,325,480 Cardinality: 50,980
11 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 138 Bytes: 1,325,480 Cardinality: 50,980
10 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 138 Bytes: 1,325,480 Cardinality: 50,980
9 INDEX FAST FULL SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT DM.DM_PRODUCT_HIERARCHY_PK :Q1001Cost: 138 Bytes: 1,325,480 Cardinality: 50,980
16 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 286 Bytes: 1,325,480 Cardinality: 50,980
15 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002Cost: 286 Bytes: 1,325,480 Cardinality: 50,980
14 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002Cost: 286 Bytes: 1,325,480 Cardinality: 50,980
13 INDEX FAST FULL SCAN INDEX PARALLEL_COMBINED_WITH_PARENT DM.DM_PRODUCT_HIERARCHY_LPK :Q1002Cost: 286 Bytes: 1,325,480 Cardinality: 50,980

{code}
Like Show 0 Likes(0)

Actions
5. Re: How i can rewrite the below query

user590978 Apr 15, 2014 6:29 PM (in response to SGUN)

Below are the no of rows for each table..

MKTRPT_VW -- 50980
HH_COUNT_FACT -- 14971116
ADVT_WRK_TBL --841554

Also, for view (MKTRPT_VW ) i need to do left outer join insted of inner join..

Please help

thanks in advance.
Like Show 0 Likes(0)

Actions
6. Re: How i can rewrite the below query

jihuyao Apr 15, 2014 7:46 PM (in response to user590978)

It is better to take another step back and check what is the none-parallel plan for joining the three tables only (PRODUCT_H, ADVT_WRK_TBL, HH_COUNT_FACT).

Since the result is group by AL2.DM_PRODUCT_HKEY, AL3.PRODUCT_YEAR it naturally makes sense to initiate the parallel process on PRODUCT_H table particularly partitioned on DM_PRODUCT_HKEY and sub-partitioned on PRODUCT_YEAR so each slave process can easily return its own grouping result. Though other business process may prevent from doing such design.

What else? Perhaps ensure enough memory for possible hash join in each slave process and analyze sql trace for major wait events.
Like Show 0 Likes(0)

Actions
7. Re: How i can rewrite the below query

user590978 Apr 16, 2014 1:06 PM (in response to jihuyao)

If we don't use parallel then cost is more and execution plan is more complex to read the data.
I am trying to use the view query itself in the query and trying to make it fast.
Is there any other way you can help me.

Thanks in advance!
Like Show 0 Likes(0)

Actions
8. Re: How i can rewrite the below query

jihuyao Apr 16, 2014 8:14 PM (in response to user590978)

Based on the plan provided the parallel process is doing extraordinary and (probably) extra things. But each slave process has to do basic sql joins which looks getting lost (and/or beyond ability to read). So a none-parallel plan at least shows how optimizer determines the join order and the join method, which may be also true to each slave process in parallel process or provide good reference.

If insist to initiate the parallel process on ADVT_WRK_TBL(AL2), you can try to partition the table on column AL2.DM_PRODUCT_HKEY and see how the plan responds.

Consider all the table volumes, the none-parallel process should not be very bad unless the cardinality is extraordinary or some resource contention exists. But it is all up to your expectation and requirement.
Like Show 0 Likes(0)

Actions
9. Re: How i can rewrite the below query

9876564 Apr 16, 2014 11:27 PM (in response to user590978)

Dont do Group by in one go ...First perform the join operations and then group by the result

e.g.

Select column1 ,
Column2,
SUM( Column3)

From (select column1, Column2, ..... From tabl1 inner join table2 on ()........)

group by Column 1,Column2
Like Show 0 Likes(0)

Actions
10. Re: How i can rewrite the below query

user590978 Apr 17, 2014 8:25 PM (in response to 9876564)

Thank you so much to all who replied and looked at my question....

I tried query as you mentioned (9876564) but it took 12 mins to complete.
Looks like its taking time for me every time to load data.

Please advice how can we move different approach to make it fast...

Thanks in advance!
Like Show 0 Likes(0)

Actions
11. Re: How i can rewrite the below query

jihuyao Apr 18, 2014 7:18 PM (in response to user590978)

The query only has simple joins and little filter so rewrite does not help much. To check the impact of data volume on performance, run a small sampling query and see how fast it goes. Something like,

with AL3 as (
select * from AL3 where rownum<=1000
)
select ...... (original query here without parallel hint)
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.