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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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...
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));

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

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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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
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.
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.
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
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 hash value: 1978759820                                                     

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

   - rule based optimizer used (consider using cbo) 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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?

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?
Christoffer SwanströmPartnerCommented:
I think my inputs would deserve at least an assist...
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.
>>> 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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.