| RBO is there in oracle 10g? [message #123565] |
Mon, 13 June 2005 23:34  |
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 #123642 is a reply to message #123577] |
Tue, 14 June 2005 04:05  |
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
