On Feb 15, 6:20 pm, Niall Litchfield <niall.litchfi..._at_dial.pipex.com>
wrote:
> 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 DBAhttp://www.orawin.info/services- Hide quoted text -
>
> - Show quoted text -
where are the statistics for the second query? You can't just look at
cost and go with that. You need to also look at statistics. I dont
know what the stats are but based on the number of steps, there is
probably a big difference.
Received on Thu Feb 15 2007 - 17:26:24 CST
