Home » RDBMS Server » Performance Tuning » SQL Tunning
SQL Tunning [message #279945] |
Mon, 12 November 2007 00:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: SQL Tunning [message #280026 is a reply to message #279946] |
Mon, 12 November 2007 06:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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?)
|
|
|
Re: SQL Tunning [message #280037 is a reply to message #280026] |
Mon, 12 November 2007 06:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: SQL Tunning [message #280097 is a reply to message #280037] |
Mon, 12 November 2007 08:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ), 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...
|
|
|
Re: SQL Tunning [message #280229 is a reply to message #280097] |
Mon, 12 November 2007 20:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Mon Sep 09 09:30:13 CDT 2024
|