Re: Query Tuning Help

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 31 Oct 2007 19:15:56 +0100
Message-ID: <4728C65C.5020808@roughsea.com>

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;
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 31 2007 - 13:15:56 CDT