|
-
RBO gone, who cares?
What is all the fuss about the Rule Based Optimizer going in 10G. I've never used it since Oracle7 and it's been an Oracle recommendation not to use it since then.
I agree with Tom Kyte all the way. Use the CBO and avoid hints like the plague (not counting APPEND and PARALLEL etc). I've seen people try to be clever with hints only to destroy the performance when data volumes change.
I've been doing this for over 8 years in many companies and I've not seen a single case where the RBO was useful for production code and I've only needed hints on a handful of very specific occasions.
Anyone think different?
Cheers
-
True Tim, I too don't care but I think many shops are still running their legacy applications using RBO on Oracle7.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Tim :
Not always CBO is good, some times it suks !!
Ok, here is just a simple eg,
Code:
SCIW_PRODUCTION_DBA> select table_name,index_name,column_position,substr(column_name, 1, 30) from dba_ind_columns
2 where table_name='WKLY_ADV_DELTA_AGGR_FIS' order by table_name,index_name,column_position;
TABLE_NAME INDEX_NAME COLUMN_POSITION SUBSTR(COLUMN_NAME,1,30)
------------------------------ ------------------------------ -------------------- ------------------------------
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 1 PH_LEVEL_NO
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 2 VER_STAT_NAME
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 3 FCST_BASE_PERIOD_DATE
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 4 ORG_LEVEL_NO
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 5 PRODUCT_TYPE
WKLY_ADV_DELTA_AGGR_FIS BIT_WKLY_ADV_DELTA_AGGR_FIS 6 ATTRIBUTE_NAME
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 1 PIN
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 2 ORG_CODE
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 3 PH_LEVEL_NO
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 4 VER_STAT_NAME
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 5 FCST_BASE_PERIOD_DATE
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 6 ORG_LEVEL_NO
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 7 PRODUCT_TYPE
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 8 REGION_CODE
WKLY_ADV_DELTA_AGGR_FIS PK_WKLY_ADV_DELTA_AGGR_FIS 9 ATTRIBUTE_NAME
15 rows selected.
SCIW_PRODUCTION_DBA> set autotrace on explain
SCIW_PRODUCTION_DBA> ed
Wrote file afiedt.buf
1 select
2 a.ITEM_DESC,
3 a.ph_level_no,
4 a.pin,a.org_name,
5 reporting_prod_hier_hist.PH_MARKETING_SORT_SEQ_NO as sortseq,
6 a.ATTRIBUTE_NAME as ATTRIBUTE_NAME,
7 a.FCST_QTY_M1-b.FCST_QTY_M1 as FCST_QTY_M1,
8 a.FCST_QTY_M2-b.FCST_QTY_M2 as FCST_QTY_M2,
9 a.FCST_QTY_M3-b.FCST_QTY_M3 as FCST_QTY_M3,
10 a.FCST_QTY_M4-b.FCST_QTY_M4 as FCST_QTY_M4,
11 a.FCST_QTY_M5-b.FCST_QTY_M5 as FCST_QTY_M5,
12 a.FCST_QTY_M6-b.FCST_QTY_M6 as FCST_QTY_M6,
13 a.ACTUAL_QTY_M1 as ACTUAL_QTY_M1,
14 a.ACTUAL_QTY_M2 as ACTUAL_QTY_M2,
15 a.ACTUAL_QTY_M3 as ACTUAL_QTY_M3
16 from
17 WKLY_ADV_DELTA_AGGR_FIS a,
18 WKLY_ADV_DELTA_AGGR_FIS b,
19 reporting_prod_hier_hist,
20 sc_product_hier_ver
21 WHERE
22 reporting_prod_hier_hist.ver_seq_no = sc_product_hier_ver.ver_seq_no AND
23 sc_product_hier_ver.eff_date <= To_Date('8/26/2003','MM/DD/YYYY') AND
24 sc_product_hier_ver.exp_date > To_Date('8/26/2003','MM/DD/YYYY') AND
25 a.PIN = reporting_prod_hier_hist.pin AND
26 a.FCST_BASE_PERIOD_DATE(+) = TO_DATE('08/01/2003', 'mm/dd/yyyy') AND
27 a.ORG_LEVEL_NO = '1' AND
28 a.PH_LEVEL_NO = '0' AND
29 a.PRODUCT_TYPE = 'UN' AND
30 a.PARENT_PIN = 'CORP' AND
31 a.PARENT_ORG_CODE = 'CORP' and
32 a.ATTRIBUTE_NAME = 'DSHIP_QTY' and
33 a.VER_STAT_NAME(+) ='COR1' and
34 b.VER_STAT_NAME(+) ='COR1' AND
35 b.FCST_BASE_PERIOD_DATE(+) = TO_DATE('08/01/2003', 'mm/dd/yyyy') AND
36 a.pin= b.pin(+) AND a.org_code = b.org_code(+) AND
37 a.attribute_name = b.attribute_name(+) AND
38 a.ph_level_no=b.ph_level_no(+) AND
39 a.org_level_no=b.org_level_no(+) AND
40 a.product_type = b.product_type(+) AND
41 a.region_code= b.region_code(+)
42 Order By
43 a.item_desc,
44 a.org_name,
45 sortseq,
46 a.org_name,
47* a.attribute_name
SCIW_PRODUCTION_DBA> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=895 Card=1 Bytes=235
)
1 0 SORT (ORDER BY) (Cost=895 Card=1 Bytes=235)
2 1 NESTED LOOPS (OUTER) (Cost=890 Card=1 Bytes=235)
3 2 NESTED LOOPS (Cost=887 Card=1 Bytes=171)
4 3 MERGE JOIN (CARTESIAN) (Cost=884 Card=1 Bytes=139)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'SC_PRODUCT_HIER_
VER' (Cost=1 Card=1 Bytes=31)
6 5 INDEX (RANGE SCAN) OF 'UK1_SC_PRODUCT_HIER_VER_B
AK' (UNIQUE) (Cost=1 Card=1)
7 4 BUFFER (SORT) (Cost=883 Card=1 Bytes=108)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'WKLY_ADV_DELTA
_AGGR_FIS' (Cost=884 Card=1 Bytes=108)
9 8 BITMAP CONVERSION (TO ROWIDS)
10 9 BITMAP INDEX (SINGLE VALUE) OF 'BIT_WKLY_ADV
_DELTA_AGGR_FIS'
11 3 TABLE ACCESS (BY INDEX ROWID) OF 'REPORTING_PROD_HIE
R_HIST' (Cost=2 Card=355664 Bytes=11381248)
12 11 INDEX (UNIQUE SCAN) OF 'PK_REPORTING_PROD_HIER_HIS
T' (UNIQUE) (Cost=1 Card=1)
13 2 TABLE ACCESS (BY INDEX ROWID) OF 'WKLY_ADV_DELTA_AGGR_
FIS' (Cost=3 Card=1 Bytes=64)
14 13 INDEX (UNIQUE SCAN) OF 'PK_WKLY_ADV_DELTA_AGGR_FIS'
(UNIQUE) (Cost=2 Card=3240)
Initially (i mean before upgrade to 9.x) we had only one index that is PK_XXXX... but after we upgraded to 9.x and started to use CBO, the same query above was going for FTS on WKLY_ADV_DELTA_AGGR_FIS ( it has around 50 million recs!! ) even after analyzing all the tables in the query ( also even after taking histograms with size 100 for all columns ). We had to build that extra index BIT_XXXX ( bit map index on low card cols )
So some times CBO suks, though most of the times its the best.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
When you upgraded, did you run "DBMS_STATS.GATHER_SYSTEM_STATS()" at all?
-
Does anybody know that the developers at Oracle Corpn removed all the RULE HINTS from their internal SQL statements?
Tamil
-
When you upgraded, did you run "DBMS_STATS.GATHER_SYSTEM_STATS()" at all?
our PRODUCTION DBA's have upgraded to 9.x and i belive they have gathered system stats.
Does anybody know that the developers at Oracle Corpn removed all the RULE HINTS from their internal SQL statements?
Tamil
Well but i have seen many posts in metalink, that oracle support peps always & strongly suggest/caution not to gather stats on any SYS/SYSTEM objects/tables...and you can see all the objects in SYS/SYSTEM tables/objects will not have any stats, so eventually they will choose RULE, then why is RULE hint needed?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi.
Gathering system stats is nothing to do with stats on the SYS schema. It gathers stats about the system (CPU, disk, memory utilization) etc. It can help when the load on the system changes during the day. If you gather system stats the CBO uses them to make more accurate predictions about costs, rather than using rules of thumb for system utilization.
As for specific problems, although I hate them it may sometimes be necessary to give the CBO a hand by using an INDEX hint. If you do, make a note of it and try to remove it in future if possible.
Cheers
Tim...
Last edited by TimHall; 08-28-2003 at 08:38 AM.
-
I've had to use the /*+ ordered use_hash() */ hint lately in a system I'm working one... otherwise the CBO chooses a Cartesian Join and never returns a result
OCP 8i, 9i DBA
Brisbane Australia
-
yeah we had had that in our software as well - its a pain as we have to use the RBO in some places and CBO just gets it wrong every time
-
Originally posted by TimHall
Hi.
Gathering system stats is nothing to do with stats on the SYS schema.
Tim...
Did I tell gathering system stats gathers stats of SYS/SYSTEM objects' ?
my post was reply for 2 diff posts ( dave and tamil )
Well in my above posted query, even with hint (Index) also, CBO was choosing FTS over Index Range Scan. To mention the query returns only around 15 to 20 rows out of that hell 5 Million recs, we had no go but to create an extra Index (we choose to have BIT Map as most of the cols were very low card cols).
Abhay.
Last edited by abhaysk; 08-28-2003 at 11:32 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
