| optimzier RULE [message #310220] |
Mon, 31 March 2008 10:50  |
lioracle
Messages: 68
Registered: February 2008
Location: Israel
|
Member |
|
|
hello
if i have index and my optimezer set "rule"
1. am i right that oracle must to use my index.?
2. the dbms.gather in oracle 10g sched is bad if i user rule?
3. i glad if you can anwer me and send me offical oracle note.
thanks
|
|
|
|
|
|
| Re: optimzier RULE [message #310275 is a reply to message #310220] |
Mon, 31 March 2008 16:35  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
some wildly unsupportable opinions of mine:
1) RULE based optimizer was great in its day. It was in fact a learnable piece of logic that would yield a predictable plan all the time if you knew it well enough, and for that it was a great way to tune by. But this was four releases ago.
2) Today's 10g/11g Cost based optimizer is actually very good. Given a good set of constraints, indexes, and statistics, it does a real good job. Like anything though the cost based optimizer has a few flaws and these you will have to discover and code around yourself. But in the end, new development should be using only the cost based approach. If you need to tune a few specific queries with RULE, then OK, but COST should be your overriding optimizer strategy.
3) RULE based optimizer is today not really there anymore. Oracle has said they "are not making any more changes to it" and has said so for some time. This of course is one of their little white lies. They in fact have made many changes to it since after Oracle 7.3. So many, that it behaves more like a dumbed down version of the Cost based optimizer rather than the RULE BASED OPTIMIZER we all knew and loved in its day. It is no longer the highly predictable animal we played with.
4) RULE based optimizer is as was stated is DUMMBED DOWN. This means that many of the great features of the database that would give you the blazing fast performance you are looking for, are not available to your query when you run a query in RULE mode. For this reason alone you should stay away from it unless you know for sure what you are doing (and most people dont'). (I used to know... but I am not so sure anymore).
Good luck, Kevin
|
|
|
|
| Re: optimzier RULE [message #310312 is a reply to message #310275] |
Mon, 31 March 2008 21:11 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I agree with almost all of Kevin's wildly unsupportable opinions.
On W.U.O.#3, I would argue that the RULE based optimiser is still as predictable as it ever was, it's just not simple to tell whether you are using it or not. I have found that when I think RBO is behaving unpredicatably, it is actually not using RBO at all.
The only "changes" I have seen to RBO are to support post-v7 object types such as partitions and (I haven't tested this) Bitmap Join Indexes. Most of the post-v7 access methods are CBO-only as Kevin points out in WUO#4.
I haven't played around with post-v7 data structures - such as object types, collections, and XML - with RULE. They may affect predictability.
I reckon that if you stick to database capabilities present in v7.3, you'll get deterministic (but sub-optimal) results from RBO.
This is of course another wildly unsupportable opinion.
Ross Leishman
|
|
|
|
| Re: optimzier RULE [message #310356 is a reply to message #310312] |
Tue, 01 April 2008 00:46 |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | The only "changes" I have seen to RBO are to support post-v7 object types such as partitions...
|
No, there is no change on this.
As soon as you use partitioning or other 8.0+ features, you switch to CBO even if you specify you work with RBO.
SQL> create table t (id integer primary key using index local, val char(100) default 'A')
2 partition by range (id)
3 (partition p1 values less than (100),
4 partition p2 values less than (200),
5 partition p3 values less than (maxvalue))
6 /
Table created.
SQL> insert into t (id) select 50+5*level from dual connect by level <= 20;
20 rows created.
SQL> set autotrace on
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> alter session set optimizer_dynamic_sampling=0;
Session altered.
SQL> select * from t where id = 120;
ID VAL
---------- ------------------------------------------------------------------------------------
120 A
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 115 | 2 (0)| 00:00:01 | 2 | 2 |
|* 2 | TABLE ACCESS FULL | T | 1 | 115 | 2 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=120)
See Cost... are there, RBO is not mentioned and partition pruning is used (Pstart/Pstop) which does not exist in RBO.
Comparing to non-partitioned table:
SQL> drop table t purge;
Table dropped.
SQL> create table t (id integer, val char(100) default 'A')
2 /
Table created.
SQL> insert into t (id) select 50+5*level from dual connect by level <= 20;
20 rows created.
SQL> set autotrace on
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> alter session set optimizer_dynamic_sampling=0;
Session altered.
SQL> select * from t where id = 120;
ID VAL
---------- -------------------------------------------------------------------
120 A
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=120)
Note
-----
- rule based optimizer used (consider using cbo)
Plan does not display anything but operations and objects and trace mentions you use RBO.
Regards
Michel
|
|
|
|
| Re: optimzier RULE [message #310637 is a reply to message #310356] |
Tue, 01 April 2008 21:08 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Excellent. This demonstrates my original point even better| Quote: | I have found that when I think RBO is behaving unpredicatably, it is actually not using RBO at all.
|
|
|
|
|
| Re: optimzier RULE [message #310638 is a reply to message #310220] |
Tue, 01 April 2008 21:56 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I recall back in the V9 days when the RBO was still officially supported that even when RULE was set via initSID.ora file;
Oracle was "forced" to use the CBO for all partitioned tables, because the RBO did not have a clue as to what should be done with partitioned data.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
