Home » RDBMS Server » Performance Tuning » RBO is there in oracle 10g?
RBO is there in oracle 10g? [message #123565] Mon, 13 June 2005 23:34 Go to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi

Is there is RBO in oracle 10g ? ...I am asking this coz few days back on this site duing one issue there was written.. in oracle 10g there is no RBO if such is the case than how query with bind variable create its execution plan as CBO cannot be used for query having bind variable.

Thank you

From:- Sunil
Re: RBO is there in oracle 10g? [message #123577 is a reply to message #123565] Tue, 14 June 2005 00:45 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Sir

Can we all have the reference where its written that in Oracle 10g CBO can't be used in case of bind variables.

...Girish
Re: RBO is there in oracle 10g? [message #123642 is a reply to message #123577] Tue, 14 June 2005 04:05 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi

girish....

what i know about CBO is as under ... please check it out sir and let me know what is wrong ... so that i can ... imporve my knowledge... thank u

WHAT CBO does ?

It finds out best way to process the SQL.
If table-> 1000 row -> using where clause values & calculate cost.

CB calculates cost for processing A SQL and it will select the least Expensive Plan.
CBO calculate 2 element to arrive at cost
• 1. CPU time.
• 2. Number of I/O.

Every query should be passed through optimizer

CBO dose not evaluate SQL with Bind variable it will ignore this SQL. (note:- i dont know about 10g in this case , or may be i am wrong on this your suggestion and guidance will be help full to me)

If we use Bind variable oracle will switch over RBO.

For CBO statistic is needed.

When ever a SQL is run CBO looks for the statistic for the entire object involved in the SQL

We must have to generate statistic for each and every object & user schema from time to time.

Either calculate statistic at object or schema level, better to use schema level because there is no possibility of leaving out any object.
CBO checks
1. Number of rows in a table
2. Length of the row
3. Number of block

Thank you,

Waiting for Reply
Sunil

Re: RBO is there in oracle 10g? [message #123652 is a reply to message #123642] Tue, 14 June 2005 04:40 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
This scenario is based on Oracle 9i

RBO is used only when Optimizer mode is choose and data dictionary conatins no statistics of any of the accessed tables.

The below table will give a clear picture as to when the CBO is used and when RBO is used, given presence/absence of statistics data.


CHOOSE
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

1. If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
2. If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
3. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

ALL_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

FIRST_ROWS_n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

FIRST_ROWS
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

Note: Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability.

RULE
The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.
Re: RBO is there in oracle 10g? [message #123653 is a reply to message #123652] Tue, 14 June 2005 04:44 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
This scenario is based on Oracle10g

Below table gives possible optimizer mode values supported by 10g

ALL_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.

FIRST_ROWS_n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.

FIRST_ROWS
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.

CHOOSE
This parameter value has been desupported. Used only for backward compatibility

RULE
This parameter value has been desupported. Used only for backward compatibility


Re: RBO is there in oracle 10g? [message #123709 is a reply to message #123565] Tue, 14 June 2005 09:21 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And as implied above, CBO will be used with bind variables.
Previous Topic: FTS even there is an index on table.
Next Topic: Index on varchar2(2500)
Goto Forum:
  


Current Time: Wed Sep 30 10:53:52 CDT 2020