Solved

Oracle optimizer

Posted on 2012-04-12
15
484 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)
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 76

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
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 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 76

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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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 34

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 34

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 73

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 34

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 84
null value 15 102
What is the version of ojdbc6.jar 2 58
Bash Script to Analyze Oracle Schemas 11 104
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

770 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