Solved

SQL Tuning

Posted on 2013-06-25
16
351 Views
Last Modified: 2013-06-28
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

0
Comment
Question by:sventhan
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
have you done an explain plan? is so please paste the results here (not an image please)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
can you provide all of the object ddl (including the tables under the view) along with the plan?
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
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
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
what was different when the plans changed?
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:ianmills2002
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Accepted Solution

by:
ianmills2002 earned 500 total points
Comment Utility
I have been looking at the 2nd part of the query, Again, you can remove the join to the sub query

LEFT JOIN  
   (
   SELECT TRAV_ON,  
      REC,  
      FREQUENCY_GRP_2 FRQ,  
      EPSILON_CUSTOMER_NUMBER  
   FROM AW.WRK_EVR_NVR_CUST_NBR_2013
   )  
   SEG  

and just join to the table AW.WRK_EVR_NVR_CUST_NBR_2013

Do this will potentially cause the query to do a full table scan of the AW.WRK_EVR_NVR_CUST_NBR_2013 table, save it in a temp table behind the scenes and then join to the temp table. Any indexes that could  be used with a normal join will be useless.

Do you need all the GROUP BY clauses in the sub queries? This depends on the relationship between the tables. If the relationship between VA and SEG is 1-to-1 or Many-to-1, then you do not need the GROUP BY clause in this part of the query.

I can see the same condition more than once in the query.
     AND AL1.PRODUCT_YEAR = 2013  
     AND AL3.CALENDAR_DATE <= TRUNC (SYSDATE, 'day') - 1

If this logic is in one subquery and you join  to another subquery that has the same logic, have you doubled up? If you are able to remove any table joins from any queries, it should improve the performance of the query as well.


In the 3rd part of the query, modify the GROUP BY slightly as the way you have it, the query could produce incorrect results.

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
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
@ 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.
0
 
LVL 6

Expert Comment

by:ianmills2002
Comment Utility
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.
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
@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

0
 
LVL 6

Expert Comment

by:ianmills2002
Comment Utility
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.
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
bitmap solved this issue. Thanks for all the wonderful help
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
agreed! ianmills2002 was doing such a fine job I dropped out.
0
 
LVL 18

Author Closing Comment

by:sventhan
Comment Utility
Gents -

Thanks for your generosity.

I guess I did the right thing this time.

sve.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now