Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cost mystery

Re: Cost mystery

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Thu, 15 Feb 2007 23:20:11 +0000
Message-ID: <45D4EAAB.7010609@dial.pipex.com>


astalavista wrote:
> Hi,
>
> Oracle 9.2.0.6
>
> I have a query wich take 2 min to execute with a cost = 8529,
> the 2nd query (equivalent) take more than 1 hour with a cost = 12
>
> How do you explain that ?
>
> Thanks
>
>
>
> SELECT /*+ ORDERED USE_NL(F) FULL(F) FULL(F_SUM) */
> f.technical_business_id,
> SUM (f_sum.num_call_itms) AS m2t_num_call_itms,
> SUM (f_sum.num_calls) AS m2t_num_calls
> FROM wx_activity_chg_period_h chg_prd,
> wc_activity_m_a f,
> wx_activity_month_m2t_h m2t_prd,
> wc_activity_m_a f_sum
> WHERE f.month_wid >= chg_prd.start_month_wid
> AND f.month_wid = m2t_prd.month_wid
> AND f.contact_wid = f_sum.contact_wid
> AND f.owner_wid = f_sum.owner_wid
> AND f.position_wid = f_sum.position_wid
> AND f.area_wid = f_sum.area_wid
> AND f.prod_wid = f_sum.prod_wid
> AND f.accnt_wid = f_sum.accnt_wid
> AND f.evt_act_attr_wid = f_sum.evt_act_attr_wid
> AND f.par_evt_act_attr_wid = f_sum.par_evt_act_attr_wid
> AND f.pr_vis_org_wid = f_sum.pr_vis_org_wid
> AND f.per_netwk_rnk_wid = f_sum.per_netwk_rnk_wid
> AND f.per_prod_rnk_wid = f_sum.per_prod_rnk_wid
> AND f_sum.month_wid BETWEEN m2t_prd.m2t_start_wid AND m2t_prd.month_wid
> GROUP BY f.technical_business_id
> ORDER BY f.technical_business_id
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8529 Card=1 Bytes=16
> 6)
>
> 1 0 SORT (GROUP BY) (Cost=8529 Card=1 Bytes=166)
> 2 1 HASH JOIN (Cost=8526 Card=1 Bytes=166)
> 3 2 TABLE ACCESS (FULL) OF 'WC_ACTIVITY_M_A' (Cost=1706 Ca
> rd=2633707 Bytes=139586471)
>
> 4 2 HASH JOIN (Cost=1758 Card=2633630 Bytes=297600190)
> 5 4 INDEX (FULL SCAN) OF 'WX_ACTIVITY_MONTH_M2T_H_M2' (N
> ON-UNIQUE) (Cost=1 Card=69 Bytes=690)
>
> 6 4 NESTED LOOPS (Cost=1708 Card=2633707 Bytes=271271821
> )
>
> 7 6 TABLE ACCESS (FULL) OF 'WX_ACTIVITY_CHG_PERIOD_H'
> (Cost=2 Card=1 Bytes=2)
>
> 8 6 TABLE ACCESS (FULL) OF 'WC_ACTIVITY_M_A' (Cost=170
> 6 Card=2633707 Bytes=266004407)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 30 db block gets
> 55952 consistent gets
> 39467 physical reads
> 0 redo size
> 168615390 bytes sent via SQL*Net to client
> 1226837 bytes received via SQL*Net from client
> 175225 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 2628347 rows processed
>
>
> SELECT f.technical_business_id,
> SUM(f_sum.num_call_itms) AS m2t_num_call_itms,
> SUM(f_sum.num_calls) AS m2t_num_calls
> FROM wx_activity_chg_period_h chg_prd,
> wx_activity_month_m2t_h m2t_prd,
> wc_activity_m_a f_sum,
> wc_activity_m_a f
> WHERE f.month_wid >= chg_prd.start_month_wid + 0
> AND f.month_wid = m2t_prd.month_wid + 0
> AND f.contact_wid = f_sum.contact_wid + 0
> AND f.owner_wid = f_sum.owner_wid + 0
> AND f.position_wid = f_sum.position_wid + 0
> AND f.area_wid = f_sum.area_wid + 0
> AND f.prod_wid = f_sum.prod_wid + 0
> AND f.accnt_wid = f_sum.accnt_wid + 0
> AND f.evt_act_attr_wid = f_sum.evt_act_attr_wid + 0
> AND f.par_evt_act_attr_wid = f_sum.par_evt_act_attr_wid + 0
> AND f.pr_vis_org_wid = f_sum.pr_vis_org_wid + 0
> AND f.per_netwk_rnk_wid = f_sum.per_netwk_rnk_wid + 0
> AND f.per_prod_rnk_wid = f_sum.per_prod_rnk_wid + 0
> AND f_sum.month_wid >= m2t_prd.m2t_start_wid + 0
> AND f_sum.month_wid <= m2t_prd.month_wid + 0
> AND f_sum.month_wid BETWEEN m2t_prd.m2t_start_wid AND f.month_wid
> AND m2t_prd.month_wid >= chg_prd.start_month_wid + 0
> AND f_sum.month_wid BETWEEN m2t_prd.m2t_start_wid AND m2t_prd.month_wid
> AND f.month_wid >= f_sum.month_wid + 0
> GROUP BY f.technical_business_id
> ORDER BY f.technical_business_id
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=166)
> 1 0 SORT (GROUP BY) (Cost=12 Card=1 Bytes=166)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'WC_ACTIVITY_M_A' (Cost
> =1 Card=1 Bytes=101)
>
> 3 2 NESTED LOOPS (Cost=10 Card=1 Bytes=166)
> 4 3 NESTED LOOPS (Cost=9 Card=57 Bytes=3705)
> 5 4 NESTED LOOPS (Cost=3 Card=3 Bytes=36)
> 6 5 TABLE ACCESS (FULL) OF 'WX_ACTIVITY_CHG_PERIOD_H
> ' (Cost=2 Card=1 Bytes=2)
>
> 7 5 TABLE ACCESS (BY INDEX ROWID) OF 'WX_ACTIVITY_MO
> NTH_M2T_H' (Cost=1 Card=3 Bytes=30)
>
> 8 7 INDEX (RANGE SCAN) OF 'WX_ACTIVITY_MONTH_M2T_H
> _M1' (NON-UNIQUE) (Cost=1 Card=3)
>
> 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'WC_ACTIVITY_M_A'
> (Cost=2 Card=16 Bytes=848)
>
> 10 9 INDEX (RANGE SCAN) OF 'WC_ACTIVITY_M_A_F5' (NON-
> UNIQUE) (Cost=73 Card=329)
>
> 11 3 INDEX (RANGE SCAN) OF 'WC_ACTIVITY_M_A_F10' (NON-UNI
> QUE) (Cost=1 Card=14)
>
>
>
>

Essentially I think that Anurag is correct as usual, but I do have the following observation.

does WX_ACTIVITY_CHG_PERIOD_H really only have 2 rows in it?

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info/services
Received on Thu Feb 15 2007 - 17:20:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US