| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> RBO vs. CBO
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.
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 Thu Mar 21 2002 - 04:38:20 CST
 |
 |