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 -> RBO vs. CBO

RBO vs. CBO

From: James A. Williams <willjamu_at_mindspring.com>
Date: Sat, 10 Aug 2002 21:37:06 GMT
Message-ID: <3c9941f2.228979@news.mindspring.com>


I am converting my last instance to CBO. I have one query that is built into a VB application supplied by a vendor (no source code or SQL supplied) that looks like the below except for the hints. Runs subsecond in RBO but takes 20 seconds in CBO. Vendor may not be able to get a timely fix. I played with the optimzer index parms and got it down to 10 seconds. We will see. Stats are fresh.

  SELECT /*+ RULE */ j.PREMISE_nbr, j.site_id, count(distinct j.PREMISE_nbr ) count_mors
  2 FROM
  3 dc_location_mors j, dn_loc_cust_xref_mors lc, da_customer_mors cp,
  4 do_loc_meter_mors lm, dh_meter_mors m, js_location Mj, it_order Mt WHERE
  5 j.premise_nbr = lc.premise_id (+) AND j.site_id = lc.site_id (+) AND
  6 lc.cis_acct_nbr = cp.cis_acct_nbr AND j.premise_nbr = lm.premise_id (+) AND
  7 j.site_id = lm.site_id (+) AND lm.meter_nbr = m.mtr_nbr (+) AND   8 Mj.location_id = Mt.location_id AND mt.ordr_stat_cd <> 'CMPL' AND
  9 Mt.appointment_dte >= '20020320' AND j.premise_nbr = Mj.unique_id AND
 10 j.site_id = Mj.building_nbr AND j.geo_area_cd is not null AND cp.ssn =
 11* '257315555' GROUP BY j.premise_nbr, j.site_id SQL> /   no rows selected  

Elapsed: 00:00:02.42  

Execution Plan


   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 SORT (GROUP BY)

   2    1     NESTED LOOPS (OUTER)
   3    2       FILTER
   4    3         NESTED LOOPS (OUTER)
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS (OUTER)
   7    6               NESTED LOOPS
   8    7                 NESTED LOOPS
   9    8                   TABLE ACCESS (FULL) OF 'IT_ORDER'
  10    8                   TABLE ACCESS (BY INDEX ROWID) OF 'JS_LOCAT
          ION'
 
  11   10                     INDEX (UNIQUE SCAN) OF 'JS_LOCATION_PK'
          (UNIQUE)
 
  12    7                 TABLE ACCESS (BY INDEX ROWID) OF 'DC_LOCATIO
          N_MORS'
 
  13   12                   INDEX (UNIQUE SCAN) OF 'DC_LOCATION_MORS_P
          K' (UNIQUE)
 
  14    6               TABLE ACCESS (BY INDEX ROWID) OF 'DO_LOC_METER
          _MORS'
 
  15   14                 INDEX (RANGE SCAN) OF 'DO_LOC_METER_MORS_IND
          02' (NON-UNIQUE)
 
  16    5             TABLE ACCESS (BY INDEX ROWID) OF 'DA_CUSTOMER_MO
          RS'
 
  17   16               INDEX (RANGE SCAN) OF 'DA_CUSTOMER_MORS_IND03'
           (NON-UNIQUE)
 
  18    4           INDEX (RANGE SCAN) OF 'DN_LOC_CUST_XREF_MORS_PK' (
          UNIQUE)
 
  19    2       INDEX (RANGE SCAN) OF 'DH_METER_MORS_IND01' (NON-UNIQU
          E)
 
 
 
 
 

Statistics


          0  recursive calls
          4  db block gets
       6283  consistent gets
       2493  physical reads
          0  redo size
        324  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
 
 

SQL>   1 SELECT /*+ FIRST_ROWS */ j.PREMISE_nbr, j.site_id, count(distinct j.PREMISE_nbr ) count_mors
  2 FROM
  3 dc_location_mors j, dn_loc_cust_xref_mors lc, da_customer_mors cp,
  4 do_loc_meter_mors lm, dh_meter_mors m, js_location Mj, it_order Mt WHERE
  5 j.premise_nbr = lc.premise_id (+) AND j.site_id = lc.site_id (+) AND
  6 lc.cis_acct_nbr = cp.cis_acct_nbr AND j.premise_nbr = lm.premise_id (+) AND
  7 j.site_id = lm.site_id (+) AND lm.meter_nbr = m.mtr_nbr (+) AND   8 Mj.location_id = Mt.location_id AND mt.ordr_stat_cd <> 'CMPL' AND
  9 Mt.appointment_dte >= '20020320' AND j.premise_nbr = Mj.unique_id AND
 10 j.site_id = Mj.building_nbr AND j.geo_area_cd is not null AND cp.ssn =
 11* '257315555' GROUP BY j.premise_nbr, j.site_id SQL> /   no rows selected  

Elapsed: 00:00:21.35  

Execution Plan


   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3877 Card=
          1 Bytes=129)
 
   1    0   SORT (GROUP BY) (Cost=3877 Card=1 Bytes=129)
   2    1     NESTED LOOPS (OUTER) (Cost=3855 Card=1 Bytes=129)
   3    2       NESTED LOOPS (OUTER) (Cost=3852 Card=1 Bytes=121)
 

   4    3         NESTED LOOPS (Cost=3849 Card=1 Bytes=101)
   5    4           HASH JOIN (Cost=3697 Card=1 Bytes=80)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'DA_CUSTOMER_MO
          RS' (Cost=5 Card=2 Bytes=42)
 
   7    6               INDEX (RANGE SCAN) OF 'DA_CUSTOMER_MORS_IND03'
           (NON-UNIQUE) (Cost=3 Card=2)
 
   8    5             HASH JOIN (OUTER) (Cost=3691 Card=13749 Bytes=81
          1191)
 
   9    8               HASH JOIN (Cost=3219 Card=13749 Bytes=494964)
  10    9                 TABLE ACCESS (FULL) OF 'JS_LOCATION' (Cost=7
          00 Card=621617 Bytes=11189106)
 
  11    9                 TABLE ACCESS (FULL) OF 'DC_LOCATION_MORS' (C
          ost=2110 Card=1813841 Bytes=32649138)
 
  12    8               INDEX (FAST FULL SCAN) OF 'DN_LOC_CUST_XREF_MO
          RS_IND01' (NON-UNIQUE) (Cost=457 Card=1579328 Bytes=36324544
          )
 
  13    4           TABLE ACCESS (FULL) OF 'IT_ORDER' (Cost=152 Card=5
          0439 Bytes=1059219)
 
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'DO_LOC_METER_MORS'
           (Cost=3 Card=1653251 Bytes=33065020)
 
  15   14           INDEX (RANGE SCAN) OF 'DO_LOC_METER_MORS_IND02' (N
          ON-UNIQUE) (Cost=2 Card=1653251)
 
  16    2       INDEX (RANGE SCAN) OF 'DH_METER_MORS_IND01' (NON-UNIQU
          E) (Cost=3 Card=1607320 Bytes=12858560)
 
 
 
 
 

Statistics


          0  recursive calls
         13  db block gets
      53593  consistent gets
      45973  physical reads
          0  redo size
        324  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          0  rows processed
 

SQL>    1 SELECT /*+ ALL_ROWS */ j.PREMISE_nbr, j.site_id, count(distinct j.PREMISE_nbr ) count_mors
  2 FROM
  3 dc_location_mors j, dn_loc_cust_xref_mors lc, da_customer_mors cp,
  4 do_loc_meter_mors lm, dh_meter_mors m, js_location Mj, it_order Mt WHERE
  5 j.premise_nbr = lc.premise_id (+) AND j.site_id = lc.site_id (+) AND
  6 lc.cis_acct_nbr = cp.cis_acct_nbr AND j.premise_nbr = lm.premise_id (+) AND
  7 j.site_id = lm.site_id (+) AND lm.meter_nbr = m.mtr_nbr (+) AND   8 Mj.location_id = Mt.location_id AND mt.ordr_stat_cd <> 'CMPL' AND
  9 Mt.appointment_dte >= '20020320' AND j.premise_nbr = Mj.unique_id AND
 10 j.site_id = Mj.building_nbr AND j.geo_area_cd is not null AND cp.ssn =
 11* '257315555' GROUP BY j.premise_nbr, j.site_id SQL> /   no rows selected  

Elapsed: 00:00:21.32  

Execution Plan


   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=3877 Card=1
          Bytes=129)
 

   1    0   SORT (GROUP BY) (Cost=3877 Card=1 Bytes=129)
   2    1     NESTED LOOPS (OUTER) (Cost=3855 Card=1 Bytes=129)
   3    2       NESTED LOOPS (OUTER) (Cost=3852 Card=1 Bytes=121)
   4    3         NESTED LOOPS (Cost=3849 Card=1 Bytes=101)
   5    4           HASH JOIN (Cost=3697 Card=1 Bytes=80)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'DA_CUSTOMER_MO
          RS' (Cost=5 Card=2 Bytes=42)
 
   7    6               INDEX (RANGE SCAN) OF 'DA_CUSTOMER_MORS_IND03'
           (NON-UNIQUE) (Cost=3 Card=2)
 
   8    5             HASH JOIN (OUTER) (Cost=3691 Card=13749 Bytes=81
          1191)
 
   9    8               HASH JOIN (Cost=3219 Card=13749 Bytes=494964)
  10    9                 TABLE ACCESS (FULL) OF 'JS_LOCATION' (Cost=7
          00 Card=621617 Bytes=11189106)
 
  11    9                 TABLE ACCESS (FULL) OF 'DC_LOCATION_MORS' (C
          ost=2110 Card=1813841 Bytes=32649138)
 
  12    8               INDEX (FAST FULL SCAN) OF 'DN_LOC_CUST_XREF_MO
          RS_IND01' (NON-UNIQUE) (Cost=457 Card=1579328 Bytes=36324544
          )
 
  13    4           TABLE ACCESS (FULL) OF 'IT_ORDER' (Cost=152 Card=5
          0439 Bytes=1059219)
 
  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'DO_LOC_METER_MORS'
           (Cost=3 Card=1653251 Bytes=33065020)
 
  15   14           INDEX (RANGE SCAN) OF 'DO_LOC_METER_MORS_IND02' (N
          ON-UNIQUE) (Cost=2 Card=1653251)
 
  16    2       INDEX (RANGE SCAN) OF 'DH_METER_MORS_IND01' (NON-UNIQU
          E) (Cost=3 Card=1607320 Bytes=12858560)
 
 
 
 
 

Statistics


          0  recursive calls
         13  db block gets
      53593  consistent gets
      45971  physical reads
          0  redo size
        324  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          0  rows processed
 

SQL>     Received on Sat Aug 10 2002 - 16:37:06 CDT

Original text of this message

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