Re: Query Tuning Help
- From: A Ebadi <ebadi01@xxxxxxxxx>
- To: Finn Jorgensen <finn.oracledba@xxxxxxxxx>
- Date: Thu, 1 Nov 2007 08:30:47 -0700 (PDT)
Thanks to everyone who replied. The version below from Finn (adding hash hint)
ran in 16 minutes!
Finn Jorgensen <finn.oracledba@xxxxxxxxx> 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@xxxxxxxxx> 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
Other related posts:
