Home » RDBMS Server » Performance Tuning » SQL Tuning Help (oracle 11g)
SQL Tuning Help [message #558381] Thu, 21 June 2012 10:37 Go to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Hello,

I am trying to load data using the following query which is taking forever to get results.Please guide me to find the bottlenecks and improve the performance as there are 5 queries like this which i need to union it and load it into one fact table.
SELECT
  CUSTOMERZB.CUSTOMER_KEY ZB,
CUSTOMERZW.CUSTOMER_KEY ZW,
   (sum(WMART.WLR_CUST_DB_MTH.ZNWSALE) + sum(WMART.WLR_CUST_DB_MTH.ZSALE) + sum(WMART.WLR_CUST_DB_MTH.ZUPGSALE)) - (sum(WMART.WLR_CUST_DB_MTH.ZNWRET) +
  sum(WMART.WLR_CUST_DB_MTH.ZRET) + sum(WMART.WLR_CUST_DB_MTH.ZUPGRET))Revenue,
  WMART.WLR_CUST_DB_MTH.CALDAY,
  WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC
  sum(WMART.WLR_CUST_DB_MTH.ZFRTRANS) ,
  sum(WMART.WLR_CUST_DB_MTH.ZCHTRANS) ,
  sum(WMART.WLR_CUST_DB_MTH.ZACTCHRG),
  DB_MATDIM.ZDBSIGNON,
  DB_MATDIM.DBCLASS_TXT
  --WMART.WLR_CUST_DB_MTH.UPDATE_TIMESTAMP

FROM
  WMART.WLR_CUST_DB_MTH,
  WMART.MATERIALDIM  SUB_MATDIM,
  WMART.MATERIALDIM  CLASS_MATDIM,
  WMART.CUSTOMER  CUSTOMERZB,
  WMART.MATERIALSALESDIM  DB_MATSALESDIM,
  WMART.TIMEDIM_MONTH_VW,
  WMART.MATERIALSALESDIM  SUB_MATSALESDIM,
  WMART.CUSTOMER  CUSTOMERZW,
  WMART.CUSTOMER  CUSTOMERSH,
  WMART.CUSTOMER  CUSTOMERSP,
  WMART.MATERIALDIM  DB_MATDIM,
  WMART.USAGEOPTIONSDIM


WHERE
  ( WMART.WLR_CUST_DB_MTH.ZWL_SUB_LOC=CUSTOMERZB.CUSTOMER_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.USAGEOPTIONDIM_KEY=WMART.USAGEOPTIONSDIM.USAGEOPTIONDIM_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.DB_MAT_KEY=DB_MATDIM.MATERIAL_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.DB_MAT_SALES_KEY=DB_MATSALESDIM.MATERIAL_SALES_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.CLASS_MAT_KEY=CLASS_MATDIM.MATERIAL_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.SUB_MAT_KEY=SUB_MATDIM.MATERIAL_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.SUB_MAT_SALES_KEY=SUB_MATSALESDIM.MATERIAL_SALES_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.ZWL_RPT_ORG=CUSTOMERZW.CUSTOMER_KEY  )
  AND  ( WMART.WLR_CUST_DB_MTH.SOLD_TO=CUSTOMERSP.CUSTOMER_KEY  )
  AND  ( WMART.TIMEDIM_MONTH_VW.TIME_DIM_KEY=WMART.WLR_CUST_DB_MTH.CALDAY  )
  AND
  SUB_MATDIM.MATERIAL_NUM  IN  (40884735, 40887249, 40902546, 40902548, 40902640, 40902852, 40902872, 40902875, 40905196, 40905198, 40905205, 40905207, 40905374, 40905376, 40905544, 40905546, 40972051, 40972053, 40976376, 40976378, 40986780, 40986885, 40987332, 40987897, 40988801, 40988829, 40989491, 40989523, 40990064, 40990648, 40990848, 40992145, 41016776, 41016779, 41018514, 41018517, 41026449, 41026512, 41026767, 41026770, 41026777, 41026806, 41026883, 41026929, 41027434, 41029592, 41054553, 41054555, 41054847, 41058222, 41059767, 41059768, 41059965, 41059966, 41098654, 41098656, 41169613, 41169615, 41169617, 41169619, 41169684, 41169685, 41169689, 41169861, 41169863, 41169865, 41169867, 41169869, 41170053, 41170055, 41170057, 41170741, 41170744, 41170800, 41170842, 41170846, 41170848, 41170850, 41170942, 41170944, 41172924, 41173655, 41176487, 41176692, 41191390, 41191412, 41191607, 41191609, 41191651, 41206779, 41207131, 41207133, 41207674, 41207677, 41207679, 41207774, 41208072, 41208074, 41208721, 41208723, 41208725)
  AND  ( CUSTOMERZB.SALESORG  =  'WEST'  )
  AND  WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC  >=  '201201'
  AND  ( CUSTOMERSP.INDUSTRY NOT IN ('PWI','AWI','CWI','FGWI','FWI','GWI')  )
  AND  ( CLASS_MATDIM.MATERIAL_NUM Not In (30004517, 33000003,40434827,40544038,40697050)  )


GROUP BY
  CUSTOMERZB.CUSTOMER_KEY ,
  CUSTOMERZW.CUSTOMER_KEY,
  WMART.WLR_CUST_DB_MTH.CALDAY,
  WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC
  DB_MATDIM.ZDBSIGNON,
  DB_MATDIM.DBCLASS_TXT
HAVING
  (
  (sum(WMART.WLR_CUST_DB_MTH.ZNWSALE) + sum(WMART.WLR_CUST_DB_MTH.ZSALE) + sum(WMART.WLR_CUST_DB_MTH.ZUPGSALE)) - (sum(WMART.WLR_CUST_DB_MTH.ZNWRET)
  + sum(WMART.WLR_CUST_DB_MTH.ZRET) + sum(WMART.WLR_CUST_DB_MTH.ZUPGRET))  !=  0
  )

Thanks,
Sree

[update: added the code tags, jw.]
[Edit: MC: code tags remove due to too width lines (10 times of my screen]

[Updated on: Wed, 30 September 2015 07:38] by Moderator

Report message to a moderator

Re: SQL Tuning Help [message #558382 is a reply to message #558381] Thu, 21 June 2012 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: SQL Tuning Help [message #558383 is a reply to message #558381] Thu, 21 June 2012 10:51 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
THis looks as though you are working in a star schema, with WLR_CUST_DB_MTH as your fact table? You need to provide a lot more information: DDL of the tables, detail of indexes, number of rows, cardinality of the columns used for joins and predicates, and so on. And, of course, the execution plan.
All this must be formatted correctly, see How to use [code] tags and make your code easier to read
The sticky post in this forum tells you what to do.

Report message to a moderator

Re: SQL Tuning Help [message #558386 is a reply to message #558383] Thu, 21 June 2012 10:58 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
Actually, I think part of the problem is that you have no join condition for WMART.CUSTOMER CUSTOMERSH, so you'll get a cartesian join to it. This is a perfect example of wjhy you should always use ISO join syntax, it makes mistakes like that obvious. Can you post an execution plan with and without customersh?

Report message to a moderator

Re: SQL Tuning Help [message #558396 is a reply to message #558383] Thu, 21 June 2012 11:43 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
I am attaching the explain plan ,DDLs with this message.

Getting the trace is tricky as it is taking forever to finish.

Let me know,what else you guys need.

[Updated on: Thu, 21 June 2012 11:46]

Report message to a moderator

Re: SQL Tuning Help [message #558398 is a reply to message #558396] Thu, 21 June 2012 11:56 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
Exactly what I told you before (and which you ignored): a cartesian join that generates trillions rows.

Report message to a moderator

Re: SQL Tuning Help [message #558411 is a reply to message #558398] Thu, 21 June 2012 13:00 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
I included the join for that table and Its working fine now. Razz

Thank you for the help guys..!

Report message to a moderator

Re: SQL Tuning Help [message #558413 is a reply to message #558411] Thu, 21 June 2012 13:07 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
Please will post the new code and plan? That is only fair, you know.

[Updated on: Thu, 21 June 2012 13:08]

Report message to a moderator

Re: SQL Tuning Help [message #558414 is a reply to message #558413] Thu, 21 June 2012 13:17 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Sorry am learning to use this forum and i dont use this often.

I attached the new explain plan and the SQL.!Hope this helps for the others like me..!

Report message to a moderator

Re: SQL Tuning Help [message #558430 is a reply to message #558414] Fri, 22 June 2012 00:08 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear Thalla,


Step 1 : Please check your tabls is properly analyzed or not.

  
  select   last_analyzed
  from     user_tables
  where    table_name = 'ABCD'


Step 2:

Check explain plan using code :


explain plan for
SELECT  CUSTOMERZB.CUSTOMER_KEY ZB,
	CUSTOMERZW.CUSTOMER_KEY ZW,
	(sum(WMART.WLR_CUST_DB_MTH.ZNWSALE) + sum(WMART.WLR_CUST_DB_MTH.ZSALE) + sum(WMART.WLR_CUST_DB_MTH.ZUPGSALE)) - (sum(WMART.WLR_CUST_DB_MTH.ZNWRET) +
	sum(WMART.WLR_CUST_DB_MTH.ZRET) + sum(WMART.WLR_CUST_DB_MTH.ZUPGRET))Revenue,
	WMART.WLR_CUST_DB_MTH.CALDAY,
	WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC
	sum(WMART.WLR_CUST_DB_MTH.ZFRTRANS) ,
	sum(WMART.WLR_CUST_DB_MTH.ZCHTRANS) ,
	sum(WMART.WLR_CUST_DB_MTH.ZACTCHRG),
	DB_MATDIM.ZDBSIGNON,
	DB_MATDIM.DBCLASS_TXT

FROM    WMART.WLR_CUST_DB_MTH,
	WMART.MATERIALDIM SUB_MATDIM,
	WMART.MATERIALDIM CLASS_MATDIM,
	WMART.CUSTOMER CUSTOMERZB,
	WMART.MATERIALSALESDIM DB_MATSALESDIM,
	WMART.TIMEDIM_MONTH_VW,
	WMART.MATERIALSALESDIM SUB_MATSALESDIM,
	WMART.CUSTOMER CUSTOMERZW,
	WMART.CUSTOMER CUSTOMERSH,
	WMART.CUSTOMER CUSTOMERSP,
	WMART.MATERIALDIM DB_MATDIM,
	WMART.USAGEOPTIONSDIM
WHERE
	( WMART.WLR_CUST_DB_MTH.ZWL_SUB_LOC=CUSTOMERZB.CUSTOMER_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.USAGEOPTIONDIM_KEY=WMART.USAGEOPTIONSDIM.USAGEOPTIONDIM_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.DB_MAT_KEY=DB_MATDIM.MATERIAL_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.DB_MAT_SALES_KEY=DB_MATSALESDIM.MATERIAL_SALES_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.CLASS_MAT_KEY=CLASS_MATDIM.MATERIAL_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.SUB_MAT_KEY=SUB_MATDIM.MATERIAL_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.SUB_MAT_SALES_KEY=SUB_MATSALESDIM.MATERIAL_SALES_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.ZWL_RPT_ORG=CUSTOMERZW.CUSTOMER_KEY )
    AND ( WMART.WLR_CUST_DB_MTH.SOLD_TO=CUSTOMERSP.CUSTOMER_KEY )
    AND ( WMART.TIMEDIM_MONTH_VW.TIME_DIM_KEY=WMART.WLR_CUST_DB_MTH.CALDAY )
    AND SUB_MATDIM.MATERIAL_NUM IN (40884735, 40887249, 40902546, 40902548, 40902640, 40902852, 40902872, 40902875, 40905196, 40905198, 40905205, 40905207, 40905374, 40905376, 40905544, 40905546, 40972051, 40972053, 40976376, 40976378, 40986780, 40986885, 40987332, 40987897, 40988801, 40988829, 40989491, 40989523, 40990064, 40990648, 40990848, 40992145, 41016776, 41016779, 41018514, 41018517, 41026449, 41026512, 41026767, 41026770, 41026777, 41026806, 41026883, 41026929, 41027434, 41029592, 41054553, 41054555, 41054847, 41058222, 41059767, 41059768, 41059965, 41059966, 41098654, 41098656, 41169613, 41169615, 41169617, 41169619, 41169684, 41169685, 41169689, 41169861, 41169863, 41169865, 41169867, 41169869, 41170053, 41170055, 41170057, 41170741, 41170744, 41170800, 41170842, 41170846, 41170848, 41170850, 41170942, 41170944, 41172924, 41173655, 41176487, 41176692, 41191390, 41191412, 41191607, 41191609, 41191651, 41206779, 41207131, 41207133, 41207674, 41207677, 41207679, 41207774, 41208072, 41208074, 41208721, 41208723, 41208725)
    AND ( CUSTOMERZB.SALESORG = 'WEST' )
    AND WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC >= '201201'
    AND ( CUSTOMERSP.INDUSTRY NOT IN ('PWI','AWI','CWI','FGWI','FWI','GWI') )
    AND ( CLASS_MATDIM.MATERIAL_NUM Not In (30004517, 33000003,40434827,40544038,40697050) )
GROUP BY CUSTOMERZB.CUSTOMER_KEY ,
         CUSTOMERZW.CUSTOMER_KEY,
	 WMART.WLR_CUST_DB_MTH.CALDAY,
	 WMART.TIMEDIM_MONTH_VW.MONTH_NUMERIC
	 DB_MATDIM.ZDBSIGNON,
	 DB_MATDIM.DBCLASS_TXT
HAVING
(
    (sum(WMART.WLR_CUST_DB_MTH.ZNWSALE) + sum(WMART.WLR_CUST_DB_MTH.ZSALE) + sum(WMART.WLR_CUST_DB_MTH.ZUPGSALE)) - (sum(WMART.WLR_CUST_DB_MTH.ZNWRET)
     + sum(WMART.WLR_CUST_DB_MTH.ZRET) + sum(WMART.WLR_CUST_DB_MTH.ZUPGRET)) != 0
)


Step 3 :

Then check if there is any full table scan on biggest table.If you are selecting 0nly 20-30% rows then
that column should be indexed properly.

Step 4 :

Then check cost of each query.

Step 5 :

then analyze the table and execute query.We hope query should run faster.

Report message to a moderator

Re: SQL Tuning Help [message #558438 is a reply to message #558414] Fri, 22 June 2012 02:28 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
Hi, man. Thanks for posting the solution, I wonder if we can do better?
You are getting plenty of index fast full scans and the full table scan of WLR_CUST_DB_MTH which all could be offloaded to smart scan. Best of all, you are getting the offloadable bloom filter at step id 76. Have you checked whether these operations actually are being offloaded? All too often, I find that Exadata is great in theory, but that offload processing is more, how can I put it, "elusive" than one might think.
Did you try creating bitmap indexes on all the foreign key columns of WLR_CUST_DB_MTH to see if you can get a proper star transformation? If that could be offloaded as a bloom filter join, then the query will fly. You do have the STAR_TRANSFORMATION=TRUE parameter set? Perhaps you could hint it, to be sure? Even with b-tree indexes, I might have hoped for dynamic b-tree conversions to bitmap and a star transform.
I see you are compressing for OLTP, why did you use that rather than hybrid columnar compression?

(I realise that your immediate problem is fixed, I just happen to be interested in tuning Exadata implementations.)

Report message to a moderator

Re: SQL Tuning Help [message #558460 is a reply to message #558438] Fri, 22 June 2012 08:52 Go to previous message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Its a one time load and but i will keep this mind for future projects which might be need something like this.

Thanks for the great explanation about the tools/concepts out there to improve the performance.

Report message to a moderator

Previous Topic: Reg Awrrpt report in oracle database
Next Topic: what's the relationship between the number of blocks and consistent gets ?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Feb 19 13:42:05 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.