• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

Oracle optimizer

In Oracle 10g/11g  SQL Query, if the tables are not having statistics does the oracle is going to use RULE based optimizer?
0
pms_reddy1
Asked:
pms_reddy1
  • 5
  • 3
  • 3
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
I believe so.  No stats, no way to use Cost Based.

Simple enough to test:  create a table with no statistics and generate an explain plan.
0
 
Christoffer SwanströmPartnerCommented:
Hmm, I thought the rule based optimizer would be chosen in such a case only up until 9i. Since 10g the optimizer does dynamic sampling on tables without statistics. I'm trying to find some documentation to support this...
0
 
slightwv (䄆 Netminder) Commented:
OK, I cannot get a test to actually 'show' it uses the RBO but without statistics, I don't see how if can compute a correct cost.

drop table tab1 purge;
create table tab1(col1 char(1));


begin
for i in 1..1000 loop
insert into tab1 values(dbms_random.string('A',1));
end loop;
end;
/

commit;
create index tab1_idx on tab1(col1);

explain plan for select * from tab1 where col1='a';
select * from table(dbms_xplan.display);

exec dbms_stats.delete_table_stats('SCOTT','TAB1');

explain plan for select * from tab1 where col1='a';
select * from table(dbms_xplan.display);

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Christoffer SwanströmPartnerCommented:
See page 5 in this document:

http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bidw-optimizer-10gr2-0208-130973.pdf

It says:


Optimizer_ mode  

The parameter optimizer_mode has a new default value of ALL_ROWS in Oracle
database 10g.  

This means the Optimizer will no longer operate under RULE mode (RBO) when
a table has no statistics. In Oracle database 10g the Optimizer operates under
ALL_ROWS mode (CBO) and will use dynamic sampling to get statistics for any
tables that do not have statistics and will use CBO.  The other possible values are
FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, and
FIRST_ROWS
0
 
slightwv (䄆 Netminder) Commented:
>>Since 10g the optimizer does dynamic sampling on tables without statistics

Maybe that is why I cannot reproduce it any more...

Looks like CBO is used unless you force the RULE hint.
0
 
Christoffer SwanströmPartnerCommented:
That's my understanding. Since 10g RBO is no longer really supported, you have to go out of your way (use a hint?) to force Oracle to use it. Otherwise CBO is always used, with dynamic sampling if no statistics are available.
0
 
sdstuberCommented:
Log on to Oracle Support and check this article which explains the decision tree of CBO vs RBO

 
Which Optimizer is Being Used ? [ID 66484.1]      
       

Since dynamic sampling (as mentioned above) is in place in 10g and higher
the fail-back to RBO no longer applies. Leaving explicit assignment of RULE as the only way to invoke it
0
 
sdstuberCommented:
So, on 11g if you really want to see RULE without forcing rule,  you can do it but it's contrived because you have to force dynamic sampling to stop

alter session set optimizer_dynamic_sampling=0;
alter session set optimizer_mode='CHOOSE';
create table no_stats_table as select * from user_objects;
exec dbms_stats.delete_table_stats(user,'NO_STATS_TABLE')
explain plan for select * from no_stats_table;
select * from table(dbms_xplan.display);

Open in new window


Deleting the stats wasn't really necessary but I did it to emphasize the fact that there were none.


This test case produced the following plan on one of my 11gR2 databases.

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1978759820                                                     

--------------------------------------------                                    
| Id  | Operation         | Name           |                                    
--------------------------------------------                                    
|   0 | SELECT STATEMENT  |                |                                    
|   1 |  TABLE ACCESS FULL| NO_STATS_TABLE |                                    
--------------------------------------------                                    

Note                                                                            
-----                                                                           
   - rule based optimizer used (consider using cbo) 

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
Here is the condensed version of what happens...

It all really goes back to the setting of OPTIMIZER_MODE.

Previously, the default was CHOOSE, which nobody tended to change.  The behavior of CHOOSE was that if there were statistics, then CBO was used, otherwise RBO was used.

If OPTIMIZER_MODE is set to RULE then RBO is used.

If OPTIMIZER_MODE is set to anything else, it is CBO and any tables that do not have stats will have dynamic sampling done.

If you supplied a hint, then CBO would be used.  Regardless of the setting of OPTIMIZER_MODE.
0
 
pms_reddy1Author Commented:
Thanks  everybody,

So,If we want to force Oracle to use the RBO only way is passing hints..

Am I right? or any other ways?

Thanks,
0
 
Christoffer SwanströmPartnerCommented:
If you want to force Oracle to use RBO in 10g or 11g, you have to set OPTIMIZER_MODE = RULE, or set OPTIMIZER_MODE = CHOOSE and dynamic sampling to 0.

May I ask why you would like to use RBO?
0
 
Christoffer SwanströmPartnerCommented:
I think my inputs would deserve at least an assist...
0
 
johnsoneSenior Oracle DBACommented:
The only way to do it would be to set OPTIMIZER_MODE to RULE at either the session or database level, or send the RULE hint.

If you really want to force RBO all the time, I would not use CHOOSE.

Also, be aware that RBO is being deprecated and will most likely be removed in coming versions.  I believe there are already situations where you cannot use RBO (new features) and CBO will be used whether you want to or not.
0
 
sdstuberCommented:
>>> The only way to do it would be to set OPTIMIZER_MODE to RULE at either the session or database level, or send the RULE hint.

that's not true,  check the example of above and the support article mentioned above

In almost all cases CBO will be used in 10g and above.  But RBO is still in the db and will be used by the optimizer even without explicitly telling to, but, as seen in the example, it's contrived

I'm fine with a split, but if we do that, probably should not be just tosse that gets a share.
0
 
johnsoneSenior Oracle DBACommented:
I did word that incorrectly.  I really meant to say "the most effective way to do it".

I believe later versions of 9 or starting in 10 is when they stopped enhancing RBO, so anything new after that is forced to use CBO.  The idea is that RBO will completely go away.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now