 | | | Subject: Re: Query Tuning Help | Subject: Re: Query Tuning Help 2007-11-01 - By Vlad Sadilovskiy
Back
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@(protected)> 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
>
>
>
<div>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
</div>
<div> </div>
<div>
orig_dt
>= TO_DATE ('20071016000000', 'yyyymmddhh24miss')<br>
AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
<br>
AND (record_typ='STOP' OR call_duration_value>0)
<br> </div>
<div>
<div>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.
</div>
<div> </div>
<div>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:
</div></div>
<div> </div>
<div>SELECT<br> billing_act_nbr
, final_tg_nbr, COUNT(*), SUM (call_duration_value),<br>
SUM (rated_cost_amt)</div>
<div> FROM <font color="#550055">cdrw.call_detail</font
></div>
<div><font color="#550055"> WHERE orig_dt >= TO_DATE ('
;20071016000000', 'yyyymmddhh24miss')<br>
AND orig_dt < TO
_DATE ('20071016010000', 'yyyymmddhh24miss')<br>
 
;AND (record_typ='STOP' OR call_duration_value>0)
<br><font color="#550055">  
; AND guid IS NOT NULL</font></font></div>
<div><font color="#550055"><font color="#550055">
AND guid NOT IN<
/font></font><br> (<span
class="q">SELECT<br>  
; guid<br>
FROM cdrw.call
_detail_cost_vero d<br>
WHERE orig_dt >= TO_DATE ('20071016000000', '
;yyyymmddhh24miss')
<br></span>
AND orig_dt < TO_DATE ('20071016010000', '
;yyyymmddhh24miss')</div>
<div>
AND GUID IS NOT NULL)</div>
<div> group by billing_act_nbr, final_tg_nbr;</div>
<div> </div>
<div>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.
<br> </div>
<div>Regarding the temp table. You could also consider using "WITH"
clause in some cases instead.</div>
<div> </div>
<div>Vlad Sadilovskiy<br>Oracle Database Tools<br>Web site: <a href="http://www
.fourthelephant.com/">http://www.fourthelephant.com</a><br>Oracle blog: <a href=
"http://vsadilovskiy.wordpress.com/">http://vsadilovskiy.wordpress.com
</a><br> </div>
<div><span class="gmail_quote">On 10/31/07, <b class="gmail_sendername"
>Stephane Faroult</b> <<a href="mailto:sfaroult@(protected)">sfaroult
@(protected)</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">Let me have a try at it ...<br><br>SELECT
a.billing_act_nbr, a.final_tg_nbr, COUNT(*), SUM<br>(a.call_duration
_value
),<br> SUM (a.rated_cost_amt)<br
> FROM cdrw.call_detail a<br>
left outer join (select guid<br>  
;
from drw.call_detail_cost_vero<br>
where orig_dt >= TO_DATE
<br>('20071016000000', 'yyyymmddhh24miss')<br>  
;
and orig_dt
< TO_DATE<br>('20071016010000', 'yyyymmddhh24miss')) b<br>
on a.guid
= b.guid<br>
WHERE a.orig_dt >= TO_DATE ('20071016000000',<br>'
;yyyymmddhh24miss')<br>  
; AND a.orig_dt < TO_DATE ('20071016010000&
#39;,<br>'yyyymmddhh24miss')<br>
AND (
a.record_typ='STOP' OR a.call_duration_value>0)<br> and
b.guid is null<br>GROUP BY a.billing_act_nbr, a.final_tg_nbr;<br><br>I always
suppress hints until proved necessary ...<br><br>Hope it works better ...<br>
<br>SF<br><br><br>A Ebadi wrote:<br>> We've been trying to tune this
query below, but to no avail. The<br>> table call_detail below is
a very large hourly partitioned table with<br>> each partition being about 2
-4GB! The other table
<br>> (call_detail_cost_vero) is relatively small.<br>><br>> The two
inner selects with minus runs fine alone in about 6-8 minutes,<br>> but the
entire query doesn't finish even after running it for many<br>
> hours! We've tried running it in parallel/no parallel and
hasn't<br>> helped. Any recommendations would be appreciated.
The explain plan<br>> is at the bottom of this e-mail also.<br>
><br>> Thanks,
<br>> Abdul<br>><br>> Environment: Sun Solaris, Oracle 10.2.0.3.0 on
RAC (4 node)<br>><br>> SELECT /*+ parallel(a,10) */<br>>
billing_act_nbr, final_tg_nbr,
COUNT(*), SUM<br>> (call_duration_value),
<br>> SUM (rated
_cost_amt)<br>> FROM cdrw.call_detail a<br>>
 
;WHERE orig_dt >= TO_DATE ('20071016000000',<br>> '
;yyyymmddhh24miss')<br>>
AND orig_dt < TO_DATE ('
;20071016010000',
<br>> 'yyyymmddhh24miss')<br>>  
;
AND (record_typ='STOP' OR call_duration
_value>0)<br>> AND guid IN (<br>>
SELECT
<br>>
guid<br>>
FROM
cdrw.call_detail c<br>>  
; WHERE orig_dt >= TO_DATE ('20071016000000&
#39;,<br>> 'yyyymmddhh24miss')<br>>
AND orig_dt
< TO_DATE ('20071016010000',<br>> 'yyyymmddhh24miss')
<br>>  
; AND
(record_typ='STOP' OR call_duration_value>0)<br>>  
; MINUS<br>>  
; SELECT<br>>
guid<br>>
FROM cdrw.call
_detail_cost_vero
d<br>>
WHERE orig_dt >= TO_DATE ('20071016000000',<br>>
'yyyymmddhh24miss')<br>>
AND orig_dt < TO_DATE (
'20071016010000',<br>> 'yyyymmddhh24miss'))
<br>> GROUP BY billing_act_nbr, final_tg_nbr;<br>><br><br><br>--<br><a
href="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org
/webpage/oracle-l</a><br><br><br></blockquote></div><br>
|
|
|