asked on

SQL Tuning

Experts -

The SQL tuning never been a easy task to do. I've this SQL running for 8 hours which can be rewritten to perform better. There are only 2 big tables involved which have less than 10 mil rows.

If this cannot be rewritten, is that possible to create a MV (summary) table in advance to reduce this SQL timings. Its just UNION ALL with repeated reference slowing down the SQL.

Thanks for you time.

Sve.

select '2013' per,  
   bk.prod,  
   seg.trav_on,  
   seg.rec,  
   seg.frq,  
   sum (bk.hh_prod) hhr,  
   sum (bk.net_pax) net_pax,  
   sum (bk.net_sales) net_sales,  
   sum (bk.gp) gp,  
   sum (0) va_gp,  
   sum (0) adv,  
   sysdate  
from  
   (  
   select al3.base_code prod,  
      al1.ecust_number,  
      sum (al2.net_corp_hh_renewal) hh_corp,  
      sum (al2.net_base_code_hh_renewal) hh_prod,  
      sum (al2.net_pax) net_pax,  
      sum (al2.net_sale_amt) net_sales,  
      sum (al2.gross_profit) gp  
   from dm.dm_customer_dim al1,  
      dm.dm_dt_fct al2,  
      dm.dm_phm_vw al3,  
      dm.dm_date_dim al4  
   where al1.dm_customer_dkey = al2.dm_customer_dkey  
  and al3.dm_product_hkey = al2.dm_product_hkey  
  and al4.dm_date_dkey = al2.dm_transaction_dt_dkey  
  and al2.product_year = 2013  
  and al4.calendar_date <= trunc (sysdate, 'day') - 1  
  and al3.base_code in ('ACM')  
   group by al3.base_code,  
      al1.ecust_number 
   )  
   bk  
left join  
   ( 
   select trav_on,  
      rec,  
      frequency_grp_2 frq,  
      ecust_number  
   from aw.wrk_evr_nvr_cust_nbr_2013 
   )  
   seg  
on bk.ecust_number = seg.ecust_number  
group by bk.prod,  
   seg.trav_on,  
   seg.rec,  
   seg.frq  
union all  
select '2013' per,  
   va.prod,  
   seg.trav_on,  
   seg.rec,  
   seg.frq,  
   sum (0) hhr,  
   sum (0) net_pax,  
   sum (0) net_sales,  
   sum (0) gp,  
   sum (va.va_gp) va_gp,  
   sum (0) adv,  
   sysdate  
from  
   (  
   select al3.base_code prod,  
      ref_cst.ecust_number,  
      sum (al2.gross_profit) va_gp  
   from dm.dm_dt_fct al2,  
      dm.dm_phm_vw al3,  
      dm.dm_date_dim al4,  
      aw.wrk_evr_nvr_cust_nbr_2013 al6,  
      dm.dm_customer_dim_epsilon_flag al7,  
      (  
      select al1.reservation_number,  
         al2.ecust_number  
      from dm.dm_dr_fct al1,  
         dm.dm_customer_dim al2,  
         dm.dm_date_dim al3  
      where al1.dm_referral_customer_dkey = al2.dm_customer_dkey  
     and al1.dm_reservation_dt_dkey = al3.dm_date_dkey  
     and al1.product_year = 2013  
     and al3.calendar_date <= trunc (sysdate, 'day') - 1  
      group by al1.reservation_number,  
         al2.ecust_number 
      )  
      ref_cst  
   where al2.reservation_number = ref_cst.reservation_number  
  and al3.dm_product_hkey = al2.dm_product_hkey  
  and al6.ecust_number = al7.ecust_number  
  and al7.dm_customer_dkey = al2.dm_customer_dkey  
  and al4.dm_date_dkey = al2.dm_transaction_dt_dkey  
  and al6.rec in ('VA')  
  and al2.product_year = 2013  
  and al4.calendar_date <= trunc (sysdate, 'day') - 1  
  and al3.base_code in ('ACM')  
   group by al3.base_code,  
      ref_cst.ecust_number 
   )  
   va  
left join  
   ( 
   select trav_on,  
      rec,  
      frequency_grp_2 frq,  
      ecust_number  
   from aw.wrk_evr_nvr_cust_nbr_2013 
   )  
   seg  
on va.ecust_number = seg.ecust_number  
group by va.prod,  
   seg.trav_on,  
   seg.rec,  
   seg.frq  
union all  
select '2013' per,  
   case  
      when al3.bc_summary = 'TMHTMR' then 'TMH'  
      when al3.bc_summary = 'YA5YA4' then 'YA4'  
      else al3.bc_summary  
   end prod,  
   al1.trav_on,  
   al1.rec,  
   al1.frequency_grp_2 frq,  
   0 hhr,  
   0 net_pax,  
   0 net_sales,  
   0 gp,  
   0 va_gp,  
   sum (al3.timed_unit_adv_cost) adv,  
   sysdate  
from aw.wrk_evr_nvr_cust_nbr_2013 al1,  
   aw.cm_adv_2013 al3  
where al1.ecust_number = al3.ecust_number  
and al3.bc_summary in ('ACM')  
group by al3.bc_summary,  
   al1.trav_on,  
   al1.rec,  
   al1.frequency_grp_2

Open in new window

PortletPaul

have you done an explain plan? is so please paste the results here (not an image please)

Sean Stuber

can you provide all of the object ddl (including the tables under the view) along with the plan?

sventhan

ASKER

Thanks SD for your time. I've attached all the DDLs and the Plan. This has 2 plans and both the plans are attached. Thanks for the help again.
sql-ee-tuning.txt

Sean Stuber

what was different when the plans changed?

sventhan

ASKER

I've to look into that. I see few SQL has multiple plans.
I know there were some hardware changes(adding more CPU) made couple of weeks ago.

Is there anything else that I can look into to see the change of plans?

Thanks.

ianmills2002

First thing I do for SQL tuning is to break up each query into it's smallest parts to attempt to find which of the queries is the slowest.

So far I have had a look at the 1st part of your query before the 1st UNION ALL. I have come up with the following. From looking at the query, I thought it a little odd having a join to a subquery that contained only one table anyway with no filters.

SELECT  '2013' PER,
        AL3.BASE_CODE PROD,
        SEG.TRAV_ON,  
        SEG.REC,  
        SEG.FREQUENCY_GRP_2 FRQ,  
        AL1.EPSILON_CUSTOMER_NUMBER,  
        SUM (AL2.NET_CORP_HH_RENEWAL) HH_CORP,  
        SUM (AL2.NET_BASE_CODE_HH_RENEWAL) HH_PROD,  
        SUM (AL2.NET_PAX) NET_PAX,  
        SUM (AL2.NET_SALE_AMT) NET_SALES,  
        SUM (AL2.GROSS_PROFIT) GP,
        0   VA_GP,  
        0   ADV,  
        SYSDATE   
FROM DM.DM_CUSTOMER_DIM AL1
        JOIN DM.DM_DAILY_TRANSACTION_FCT AL2
            ON AL1.DM_CUSTOMER_DKEY = AL2.DM_CUSTOMER_DKEY
        JOIN AW.WRK_EVR_NVR_CUST_NBR_2013 SEG
            ON AL1.EPSILON_CUSTOMER_NUMBER = SEG.EPSILON_CUSTOMER_NUMBER 
        JOIN DM.DM_PRODUCT_HIERARCHY_MKTRPT_VW AL3
            ON AL3.DM_PRODUCT_HKEY = AL2.DM_PRODUCT_HKEY
        JOIN DM.DM_DATE_DIM AL4
            ON AL4.DM_DATE_DKEY = AL2.DM_TRANSACTION_DT_DKEY
WHERE AL2.PRODUCT_YEAR = 2013  
  AND AL4.CALENDAR_DATE <= TRUNC (SYSDATE, 'day') - 1  
  AND AL3.BASE_CODE IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 'ELB' 
      , 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA4', 'NEL', 'TMH', 'FCT',  
      'MYR')
GROUP BY
        AL3.BASE_CODE PROD,
        SEG.TRAV_ON,  
        SEG.REC,  
        SEG.FREQUENCY_GRP_2,  
        AL1.EPSILON_CUSTOMER_NUMBER

Open in new window

I will have a look at the other queries soon if I get the chance.

PortletPaul

I started the same (didn't finish) - and agree I couldn't see any advantage in that subquery

? keep going :)

for what it's worth I placed the 3 queries side-by-side to look for commonality (if any)
attached

have not looked further I'm afraid
Q-28166890-3-q-sideby.xlsx

ASKER CERTIFIED SOLUTION

ianmills2002

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

ASKER

@ ianmills2002

Thanks so much for you time on this long SQL.

I agree with you with the SUBQ. When I merge the Sub Query with the main SQL it just ran in 28 secs. If I use that left join it runs for hours.

I'm looking at you other notes. Thanks again for you time.

sve.

ianmills2002

That is a big difference.

So I assume that AW.WRK_EVR_NVR_CUST_NBR_2013 is one of the tables with millions of rows?

Hopefully the rest is helpful.

sventhan

ASKER

@ianmills2002

Sorry, I spoke soon.

This is the 3rd part of this SQL which is slowing down the performance. The rest of the SQLs running fine less than 4 secs without any modifications.

This table CM_ADV_2013 has 400 mil rows. I do see full tbl scans.

I'm planning a bitmap index on BASE_CODE_SUMMARY as it has only 80 distinct values.

Any ideas?

SELECT '2013' PER,  
   CASE  
      WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR' THEN 'TMH'  
      WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4' THEN 'YA4'  
      ELSE AL3.BASE_CODE_SUMMARY  
   END PROD,  
   AL1.TRAV_ON,  
   AL1.REC,  
   AL1.FREQUENCY_GRP_2 FRQ,  
   0 HHR,  
   0 NET_PAX,  
   0 NET_SALES,  
   0 GP,  
   0 VA_GP,  
   SUM (AL3.TIMED_UNIT_ADV_COST) ADV,  
   SYSDATE  
FROM AW.WRK_EVR_NVR_CUST_NBR_2013 AL1,  
   AW.CM_ADV_2013 AL3  
WHERE AL1.EPSILON_CUSTOMER_NUMBER = AL3.EPSILON_CUSTOMER_NUMBER  
AND AL3.BASE_CODE_SUMMARY IN ('ACM', 'EID', 'NIT', 'SPE', 'BCT', 'SGE', 'SAB', 'LBS', 'SEN', 'EDR', 
   'ELB', 'OZZ', 'RON', 'SHH', 'UKR', 'BOT', 'HOI', 'MPG', 'UNX', 'RAC', 'YA5YA4', 'NEL', 'TMHTMR', 
   'FCT', 'MYR')  
GROUP BY 
   CASE  
      WHEN AL3.BASE_CODE_SUMMARY = 'TMHTMR' THEN 'TMH'  
      WHEN AL3.BASE_CODE_SUMMARY = 'YA5YA4' THEN 'YA4'  
      ELSE AL3.BASE_CODE_SUMMARY  
   END,
   AL1.TRAV_ON,  
   AL1.REC,  
   AL1.FREQUENCY_GRP_2

Open in new window

Plan
SELECT STATEMENT  ALL_ROWSCost: 797,127  Bytes: 314,412  Cardinality: 5,516  			
	4 HASH GROUP BY  Cost: 797,127  Bytes: 314,412  Cardinality: 5,516  		
		3 HASH JOIN  Cost: 721,747  Bytes: 11,840,854,530  Cardinality: 207,734,290  	
			1 TABLE ACCESS FULL TABLE AW.WRK_EVR_NVR_CUST_NBR_2012 Cost: 10,498  Bytes: 252,230,650  Cardinality: 7,206,590  
			2 TABLE ACCESS FULL TABLE AW.CM_ADV_2012_PIT Cost: 347,990  Bytes: 4,570,154,380  Cardinality: 207,734,290

Open in new window

ianmills2002

At the very least, an index with column EPSILON_CUSTOMER_NUMBER on the table AW.CM_ADV_2013 should come in handy.

Also adding the column BASE_CODE_SUMMARY to the same index could bring some more improvement.

I see your Plan in the last post references tables with 2012 not 2013.

sventhan

ASKER

bitmap solved this issue. Thanks for all the wonderful help

PortletPaul

agreed! ianmills2002 was doing such a fine job I dropped out.

sventhan

ASKER

Gents -

Thanks for your generosity.

I guess I did the right thing this time.

sve.