Solved

Oracle optimizer

Posted on 2012-04-12
15
480 Views
Last Modified: 2012-06-27
In Oracle 10g/11g  SQL Query, if the tables are not having statistics does the oracle is going to use RULE based optimizer?
0
Comment
Question by:pms_reddy1
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 8

Expert Comment

by:Christoffer Swanström
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 

Author Comment

by:pms_reddy1
Comment Utility
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
Comment Utility
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
 
LVL 8

Expert Comment

by:Christoffer Swanström
Comment Utility
I think my inputs would deserve at least an assist...
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> 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
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now