Re: Query Tuning Help
- From: "Vlad Sadilovskiy" <vlovsky@xxxxxxxxx>
- To: sfaroult@xxxxxxxxxxxx
- Date: Thu, 1 Nov 2007 11:02:14 -0400
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@xxxxxxxxxxxx> 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;
> >
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
