Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle optimizer

Posted on 2012-04-12
15
Medium Priority
?
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37837153
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
ID: 37837190
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37837195
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Christoffer Swanström
ID: 37837200
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37837203
>>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
ID: 37837220
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 74

Expert Comment

by:sdstuber
ID: 37837405
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37837490
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 35

Expert Comment

by:johnsone
ID: 37837576
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
ID: 37841705
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
ID: 37841720
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
ID: 37842111
I think my inputs would deserve at least an assist...
0
 
LVL 35

Expert Comment

by:johnsone
ID: 37842692
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 74

Expert Comment

by:sdstuber
ID: 37842964
>>> 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 35

Expert Comment

by:johnsone
ID: 37843127
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

670 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