Re: Cost mystery

On Feb 14, 3:07 pm, "astalavista" <nob..._at_nowhere.com> 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)

Many reasons:
* Oracle statistics does not reflect reality. * You did not compute system stats ... the optimizer_* parameters   are rough estimates by which oracle computes cost. They might   not be reflecting reality.
* Oracle CBO is not perfect (yet) :)

Thats why many folks tell you that cost has nothing to do with time taken for query to execute.
If the cost alwsys accurately reflected time, ALL queries would run FASTEST when not hinted .. read your post in relation to this statement.

Anurag Received on Wed Feb 14 2007 - 15:17:15 CST