Cost mystery

From: astalavista <nobody_at_nowhere.com>
Date: Wed, 14 Feb 2007 21:07:00 +0100
Message-ID: <45d36bee$0$15939$426a74cc@news.free.fr>

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)

Received on Wed Feb 14 2007 - 14:07:00 CST