Re: Query Tuning Help

Too many unknowns. And hence too many suggestions can be made. It is hard to estimate what's the best path without looking at statistics, i.e. CARDINALITY figures. According to the plan shown, the sub query is executed for every row that is gotten from "CALL_DETAIL" after applying filters

                         orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss')
               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
               AND (record_typ='STOP' OR call_duration_value>0)

 Suppose, fetching from "CALL_DETAIL" by GUID is more preferable. You should see why the sub query is not made the outer (first) row source. Several things might go wrong, i.e. cost of the sub query could be too low or the filtered CARDINALITY of the "CALL_DETAIL" by the rest of the predicates could be too low, or these two factors combined.

On the other hand, it's not possible to say exactly how query could be better rewritten when the structure of the tables and the statistics aren't known. If my eyes aren't fooling me, then the query can be also rewritten into NOT IN form:

SELECT

         billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value),
         SUM (rated_cost_amt)
     FROM cdrw.call_detail
   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 IS NOT NULL
               AND guid NOT IN
          (SELECT
                   guid
              FROM cdrw.call_detail_cost_vero d
             WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')

               AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
               AND GUID IS NOT NULL)
     group by billing_act_nbr, final_tg_nbr;

NOT EXISTS transformation has been already proposed by Hrishy. But these two forms can also go the "FILTER" path. So, you need to see why that is. However, it is more likely to be an ANTI JOIN in case sub query is of high CARDINALITY. The outer join trick by SF also is good depending on the situation with the statistics. Null values would need to be filtered if GUID can be null.

Regarding the temp table. You could also consider using "WITH" clause in some cases instead.

Vlad Sadilovskiy
Oracle Database Tools
Web site: http://www.fourthelephant.com
Oracle blog: http://vsadilovskiy.wordpress.com

On 10/31/07, Stephane Faroult <sfaroult_at_roughsea.com> wrote:
>
> 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
>
>
>

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