Re: Query Tuning Help
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ebadi01@xxxxxxxxx
- Date: Wed, 31 Oct 2007 19:15:56 +0100
Let me have a try at it ...
SELECT a.billing_act_nbr, a.final_tg_nbr, COUNT(*), SUM
(a.call_duration_value),
SUM (a.rated_cost_amt)
FROM cdrw.call_detail a
left outer join (select guid
from drw.call_detail_cost_vero
where orig_dt >= TO_DATE
('20071016000000', 'yyyymmddhh24miss')
and orig_dt < TO_DATE
('20071016010000', 'yyyymmddhh24miss')) b
on a.guid = b.guid
WHERE a.orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss')
AND a.orig_dt < TO_DATE ('20071016010000',
'yyyymmddhh24miss')
AND (a.record_typ='STOP' OR a.call_duration_value>0)
and b.guid is null
GROUP BY a.billing_act_nbr, a.final_tg_nbr;
I always suppress hints until proved necessary ...
Hope it works better ...
SF
A Ebadi wrote:
We've been trying to tune this query below, but to no avail. The
table call_detail below is a very large hourly partitioned table with
each partition being about 2-4GB! The other table
(call_detail_cost_vero) is relatively small.
The two inner selects with minus runs fine alone in about 6-8 minutes,
but the entire query doesn't finish even after running it for many
hours! We've tried running it in parallel/no parallel and hasn't
helped. Any recommendations would be appreciated. The explain plan
is at the bottom of this e-mail also.
Thanks,
Abdul
Environment: Sun Solaris, Oracle 10.2.0.3.0 on RAC (4 node)
SELECT /*+ parallel(a,10) */
billing_act_nbr, final_tg_nbr, COUNT(*), SUM
(call_duration_value),
SUM (rated_cost_amt)
FROM cdrw.call_detail a
WHERE orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss')
AND orig_dt < TO_DATE ('20071016010000',
'yyyymmddhh24miss')
AND (record_typ='STOP' OR call_duration_value>0)
AND guid IN (
SELECT
guid
FROM cdrw.call_detail c
WHERE orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss')
AND orig_dt < TO_DATE ('20071016010000',
'yyyymmddhh24miss')
AND (record_typ='STOP' OR call_duration_value>0)
MINUS
SELECT
guid
FROM cdrw.call_detail_cost_vero d
WHERE orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss')
AND orig_dt < TO_DATE ('20071016010000',
'yyyymmddhh24miss'))
GROUP BY billing_act_nbr, final_tg_nbr;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
