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

Re: RBO vs. CBO

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Sun, 11 Aug 2002 13:23:11 -0400
Message-ID: <sEy59.131328$D8.4617200@news4.srv.hcvlny.cv.net>


James,

If you are finding that RBO provides the best response times *and* you are not able to
get the same execution plan by analyzing + creating histograms etc., then you might want to look into stored outlines. You can thus create a stored outline for the query by running it under RBO. Then under CBO, it will run under the RBO execution plan. Read up on it by doing a search in tahiti.oracle.com

Anurag

"James A. Williams" <willjamu_at_mindspring.com> wrote in message news:3c9941f2.228979_at_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 Sun Aug 11 2002 - 12:23:11 CDT

Original text of this message

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