 | | | Subject: Re: Query Tuning Help | Subject: Re: Query Tuning Help 2007-11-01 - By A Ebadi
Back
Thanks to everyone who replied. The version below from Finn (adding hash hint)
ran in 16 minutes!
Finn Jorgensen <finn.oracledba@(protected)> wrote:
The biggest problem for this query is that the IN results in a FILTER
operation, which is very slow, and I'm guessing the inner "MINUS" query returns
a lot of rows.
I'm guessing there's no index on "guid" in call_detail?
Try this version (it's hard tuning queries without access to the database so
a little testing can be done) :
SELECT /*+ parallel(a,10) use_hash(a b) */
billing_act_nbr, final_tg_nbr, COUNT(*), SUM (call_duration_value),
SUM (rated_cost_amt)
FROM cdrw.call_detail a,
( 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')) b
WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
AND (record_typ='STOP' OR call_duration_value>0)
AND a.guid = b.guid
GROUP BY billing_act_nbr, final_tg_nbr;
Finn
On 10/31/07, A Ebadi <ebadi01@(protected)> 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;
EXPLAIN PLAN
QUERY_PLAN
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---
SELECT STATEMENT Cost = 32
2.1 HASH GROUP BY
3.1 FILTER
4.1 PX COORDINATOR
5.1 PX SEND QC (RANDOM) ":TQ10000"
6.1 PX BLOCK ITERATOR
7.1 TABLE ACCESS FULL "CALL_DETAIL" TABLE
4.2 MINUS
5.1 SORT UNIQUE NOSORT
6.1 PARTITION RANGE SINGLE
7.1 TABLE ACCESS BY LOCAL INDEX ROWID "CALL_DETAIL" TABLE
QUERY_PLAN
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---
8.1 INDEX RANGE SCAN "CALL_DETAIL_UK" INDEX (UNIQUE)
5.2 SORT UNIQUE NOSORT
6.1 PARTITION RANGE SINGLE
7.1 INDEX RANGE SCAN "CALL_DETAIL_COST_VERO_PK" INDEX (UNIQUE)
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
<div>Thanks to everyone who replied. The version below from Finn (adding
hash hint) ran in 16 minutes!</div> <div><BR><BR><B><I>Finn Jorgensen <finn
.oracledba@(protected)></I></B> wrote:</div> <BLOCKQUOTE class=replbq style=
"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"> <DIV>The
biggest problem for this query is that the IN results in a FILTER operation,
which is very slow, and I'm guessing the inner "MINUS" query returns a lot of
rows.</DIV> <DIV> </DIV> <DIV>I'm guessing there's no index on "guid" in
call_detail?</DIV> <DIV> </DIV> <DIV>Try this version (it's hard tuning
queries without access to the database so a little testing can be done) :</DIV>
<DIV> </DIV> <DIV>SELECT /*+ parallel(a,10) use_hash(a b) */<BR>
billing_act_nbr, final_tg_nbr, COUNT(
*), SUM (call_duration_value),<BR>
SUM
(rated_cost_amt)<BR> FROM cdrw.call_detail a,</DIV> <DIV>(
SELECT<BR>
guid<BR>  
; FROM cdrw.call_detail c<BR>
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>
MINUS<BR>
SELECT<BR>
guid<BR>  
; FROM cdrw.call_detail_cost
_vero d<BR>
WHERE orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss') <BR>
AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')) b<BR>
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> AND a.guid
= b.guid</DIV> <DIV>GROUP BY billing_act_nbr, final_tg_nbr;</DIV> <DIV><BR>
</DIV> <DIV>Finn<BR> </DIV> <DIV><SPAN class=gmail_quote>On 10/31
/07, <B class=gmail_sendername>A Ebadi</B> <<A href="mailto:ebadi01@(protected)
">ebadi01@(protected)</A>> wrote:</SPAN> <BLOCKQUOTE class=gmail_quote style=
"PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV>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. </DIV> <DIV> </DIV> <DIV>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. </DIV> <DIV> </DIV> <DIV>Thanks,</DIV> <DIV
>Abdul</DIV> <DIV> </DIV> <DIV>Environment: Sun Solaris, Oracle 10.2.0.3
.0 on RAC (4 node)</DIV> <DIV> </DIV> <DIV>SELECT /*+ parallel(a,10) */
<BR> billing_act_nbr, final_tg
_nbr, COUNT(*), SUM (call_duration_value),<BR>
SUM (rated_cost_amt)<BR> FROM cdrw.call
_detail a<BR>
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>
AND guid IN (<BR>
SELECT <BR>
guid<BR>  
; FROM cdrw.call
_detail c<BR>
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>
MINUS<BR>
SELECT<BR>
guid<BR
>
FROM cdrw.call_detail_cost_vero d<BR>
WHERE orig_dt >= TO_DATE ('20071016000000',
'yyyymmddhh24miss') <BR>
AND orig_dt < TO_DATE ('20071016010000',
'yyyymmddhh24miss'))<BR>GROUP BY billing_act_nbr, final_tg_nbr;</DIV> <DIV>
</DIV> <DIV> </DIV> <DIV>EXPLAIN
PLAN</DIV> <DIV>QUERY_PLAN<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----<BR>
SELECT STATEMENT Cost = 32<BR> 2.1 HASH GROUP BY<BR>
3.1 FILTER<BR> 4.1 PX
COORDINATOR<BR> 5.1 PX SEND QC
(RANDOM) ":TQ10000"<BR> 6
.1 PX BLOCK ITERATOR<BR>
7.1 TABLE ACCESS FULL "CALL_DETAIL" TABLE<BR>
4.2 MINUS<BR> 5.1 SORT
UNIQUE NOSORT <BR> 6.1
PARTITION RANGE SINGLE<BR>  
; 7.1 TABLE ACCESS BY LOCAL INDEX ROWID "CALL_DETAIL" TABLE</DIV>
<DIV>QUERY_PLAN<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---<BR>
8.1 INDEX
RANGE SCAN "CALL_DETAIL_UK" INDEX (UNIQUE)<BR>
5.2 SORT UNIQUE NOSORT<BR>  
; 6.1 PARTITION RANGE SINGLE<BR>
7.1 INDEX RANGE SCAN "CALL_DETAIL_COST_VERO_PK"
INDEX (UNIQUE)<BR> </DIV><SPAN class=ad> <DIV> </DIV> <div>__ _____
__ ____ ____ ____ ____ ____ ____ ____ ____<BR>Do You Yahoo!?<BR>Tired of spam?
Yahoo! Mail has the best spam protection around <BR><A onclick="return top.js
.OpenExtLink(window,event,this)" href="http://mail.yahoo.com/" target=_blank
>http://mail.yahoo.com</A>
</div></SPAN></BLOCKQUOTE></DIV><BR></BLOCKQUOTE><BR><p> __ ____ ____ ____
__ ____ ____ ____ ____ ____ _____<br>Do You Yahoo!?<br>Tired of spam? Yahoo!
Mail has the best spam protection around <br>http://mail.yahoo.com
|
|
|