Re: Query Tuning Help

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Wed, 31 Oct 2007 16:35:20 +0000 (GMT)
Message-ID: <669604.57554.qm@web27809.mail.ukl.yahoo.com>

Hi

You can also try

SELECT 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 NOT EXISTS
(SELECT 'X'
from cdrw.call_detail_cost_vero d
WHERE a.guid=d.guid
AND orig_dt >= TO_DATE ('20071016000000', 'yyyymmddhh24miss')
AND orig_dt < TO_DATE ('20071016010000', 'yyyymmddhh24miss')
)
group by billing_act_nbr, final_tg_nbr

regards
Hrishy
--- Finn Jorgensen <finn.oracledba_at_gmail.com> 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_at_yahoo.com> 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
> >
>

---

Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now.
http://uk.answers.yahoo.com/

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

Received on Wed Oct 31 2007 - 11:35:20 CDT