OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL Tunning

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Tunning [message #279945]

Mon, 12 November 2007 00:53

M.Shakeel Azeem
Messages: 226
Registered: September 2006

Senior Member

Dear All,
i had a query with joins of 2-tables and this query returns with results withing 2-minutes but one of developer want to add another table but if he add another table then the query takes a long time 2-hour and so
So i had been given the responsiblity to resolve the issue
Let me explain
Before

SELECT ff.date_from, ff.bsc_id, ff.bts,
ROUND ((SUM (ff.drp_rto) / COUNT (ff.drp_rto)), 2) drp_rto_cs,
ROUND ((SUM (dd.drp_rto) / COUNT (dd.drp_rto)), 2) drp_rto_ps,
ROUND ((SUM (ff.avg_dly) / COUNT (ff.avg_dly)), 2) avg_dly_cs,
ROUND ((SUM (dd.avg_dly) / COUNT (dd.avg_dly)), 2) avg_dly_ps,
ROUND ((SUM (ff.cpl_rto) / COUNT (ff.cpl_rto)), 2) cpl_rto_org,
FROM bss.b_traf_org ff, bss.b_data_org dd
WHERE ff.bts = dd.bts
AND ff.date_from = dd.date_from
AND ff.bsc_id = dd.bsc_id
AND TO_DATE (ff.date_from, 'RRRR-MM-DD') BETWEEN TO_DATE ('2007-05-20', 'RRRR-MM-DD' )
AND TO_DATE ('2007-05-20','RRRR-MM-DD' )
GROUP BY ff.date_from, ff.bts, ff.bsc_id

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 210 357
SORT GROUP BY 210 7 K 357
FILTER
HASH JOIN 39 K 1 M 222
TABLE ACCESS FULL BSS.B_TRAF_ORG 204 4 K 120
TABLE ACCESS FULL BSS.B_DATA_ORG 81 K 1 M 101

After

SELECT ff.date_from, ff.bsc_id, ff.bts,
ROUND ((SUM (ff.drp_rto) / COUNT (ff.drp_rto)), 2) drp_rto_cs,
ROUND ((SUM (dd.drp_rto) / COUNT (dd.drp_rto)), 2) drp_rto_ps,
ROUND ((SUM (ff.avg_dly) / COUNT (ff.avg_dly)), 2) avg_dly_cs,
ROUND ((SUM (dd.avg_dly) / COUNT (dd.avg_dly)), 2) avg_dly_ps,
ROUND ((SUM (ff.cpl_rto) / COUNT (ff.cpl_rto)), 2) cpl_rto_org,
ROUND ((SUM (cc.cpl_rto) / COUNT (cc.cpl_rto)), 2) cpl_rto_ter
FROM bss.b_traf_org ff, bss.b_data_org dd, bss.b_traf_ter cc
WHERE ff.bts = dd.bts
AND ff.date_from = dd.date_from
AND ff.bsc_id = dd.bsc_id
AND ff.bts = cc.bts
AND ff.date_from = cc.date_from
AND ff.bsc_id = cc.bsc_id AND TO_DATE (ff.date_from, 'RRRR-MM-DD') BETWEEN TO_DATE ('2007-05-20', 'RRRR-MM-DD')
AND TO_DATE ('2007-05-20','RRRR-MM-DD' )
GROUP BY ff.date_from, ff.bts, ff.bsc_id

Explain Plan

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 210 922
SORT GROUP BY NOSORT 210 11 K 922
FILTER
MERGE JOIN 7 M 407 M 922
MERGE JOIN 39 K 1 M 518
SORT JOIN 204 4 K 122
TABLE ACCESS FULL BSS.B_TRAF_ORG 204 4 K 120
SORT JOIN 81 K 1 M 397
TABLE ACCESS FULL BSS.B_DATA_ORG 81 K 1 M 101
SORT JOIN 81 K 1 M 404
TABLE ACCESS FULL BSS.B_TRAF_TER 81 K 1 M 108

Now i want to avoid full table scan ,i had tried indexes with different Combinations but unable to resolve the issue
Will u please help me in this regard?

Thanx in Advance

Report message to a moderator

Re: SQL Tunning [message #279946 is a reply to message #279945]

Mon, 12 November 2007 00:57

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

144 posts and you still don't know the basic:

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: SQL Tunning [message #280026 is a reply to message #279946]

Mon, 12 November 2007 06:04

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Slightly off topic (not directly related to the decreased performance), but:
In both statements, it says

TO_DATE (ff.date_from, 'RRRR-MM-DD')

so, this ff.date_from is not a date? Why not?

Another question: are there any indexes on these tables? (and now that I'm at it: statistics including index statistics are up to date, I assume?)

Report message to a moderator

Re: SQL Tunning [message #280037 is a reply to message #280026]

Mon, 12 November 2007 06:13

M.Shakeel Azeem
Messages: 226
Registered: September 2006

Senior Member

Dear skooman

yes u r right, ff.date_from is not a date ,its varchar2
there are about 80000 records in this table so we can't change it

There are no indexes defined on these tables but query with 2-tables was fine ,but not the case when i add third table
Table Statistics are upto-date

Report message to a moderator

Re: SQL Tunning [message #280097 is a reply to message #280037]

Mon, 12 November 2007 08:28

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

M.Shakeel Azeem wrote on Mon, 12 November 2007 07:43

there are about 80000 records in this table so we can't change it

I don't see why you can't change it (even if it were billions of rows, it's not like you have to do the update by hand Wink

), but okay, that's up to you.

I'm not sure about what indexes would be best in this case (which was your original question). Hopefully someone else on this forum will step in now.

As a courtesy, here's you code formatted, please do this yourself next time:

SELECT ff.date_from
      ,ff.bsc_id
      ,ff.bts
      ,round((SUM(ff.drp_rto) / COUNT(ff.drp_rto)), 2) drp_rto_cs
      ,round((SUM(dd.drp_rto) / COUNT(dd.drp_rto)), 2) drp_rto_ps
      ,round((SUM(ff.avg_dly) / COUNT(ff.avg_dly)), 2) avg_dly_cs
      ,round((SUM(dd.avg_dly) / COUNT(dd.avg_dly)), 2) avg_dly_ps
      ,round((SUM(ff.cpl_rto) / COUNT(ff.cpl_rto)), 2) cpl_rto_org
      ,
FROM   bss.b_traf_org ff
      ,bss.b_data_org dd
WHERE  ff.bts = dd.bts
AND    ff.date_from = dd.date_from
AND    ff.bsc_id = dd.bsc_id
AND    to_date(ff.date_from, 'RRRR-MM-DD') BETWEEN
       to_date('2007-05-20', 'RRRR-MM-DD') AND
       to_date('2007-05-20', 'RRRR-MM-DD')
GROUP  BY ff.date_from
         ,ff.bts
         ,ff.bsc_id
         
SELECT ff.date_from
      ,ff.bsc_id
      ,ff.bts
      ,round((SUM(ff.drp_rto) / COUNT(ff.drp_rto)), 2) drp_rto_cs
      ,round((SUM(dd.drp_rto) / COUNT(dd.drp_rto)), 2) drp_rto_ps
      ,round((SUM(ff.avg_dly) / COUNT(ff.avg_dly)), 2) avg_dly_cs
      ,round((SUM(dd.avg_dly) / COUNT(dd.avg_dly)), 2) avg_dly_ps
      ,round((SUM(ff.cpl_rto) / COUNT(ff.cpl_rto)), 2) cpl_rto_org
      ,round((SUM(cc.cpl_rto) / COUNT(cc.cpl_rto)), 2) cpl_rto_ter
FROM   bss.b_traf_org ff
      ,bss.b_data_org dd
      ,bss.b_traf_ter cc
WHERE  ff.bts = dd.bts
AND    ff.date_from = dd.date_from
AND    ff.bsc_id = dd.bsc_id
AND    ff.bts = cc.bts
AND    ff.date_from = cc.date_from
AND    ff.bsc_id = cc.bsc_id
AND    to_date(ff.date_from, 'RRRR-MM-DD') BETWEEN
       to_date('2007-05-20', 'RRRR-MM-DD') AND
       to_date('2007-05-20', 'RRRR-MM-DD')
GROUP  BY ff.date_from
         ,ff.bts
         ,ff.bsc_id

PS this shows that the code you posted can not be the actual code you used, because there is a spare comma in the first statement...

Report message to a moderator

Re: SQL Tunning [message #280229 is a reply to message #280097]

Mon, 12 November 2007 20:23

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

The significant difference between the plans - other than the extra table - is that the second one is performing SORT MERGE joins, whereas the first one performs a HASH join. But this is not important because we are talking abour such small volumes (81000 rows).

With such small volumes, it should NOT be taking 2 hours.

Either:

The data volumes are wrong (your plan says the tables are 204, 81000, 81000 rows respectively)
You are joining on a non-unique key.

Check the tables. Are these row counts right?
Check your joins. Is (dd.date_from, dd.bsc_id) unique? Is (cc.bts, cc.date_from, cc.bsc_id) unique? I think this is most likely the problem.

You can try an indexed approach. It may be faster, but I don't think it will address the problem (that you have an incompatible combination of joins in the query). You would need indexes on:

ff.date_from
dd.bsc_id, dd.date_from
cc.bsc_id, cc.bts, cc.date_from

Ross Leishman

Report message to a moderator

Previous Topic:	BUFFER CACHE
Next Topic:	Buffer Cache

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 19:53:18 CST 2024