What is the difference between Rule based Optimizer and Cost based Optimizer in oracle?
In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db.
A long time ago the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically the RBO used a set of rules to determine how to execute a query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example assume someone put an index on the GENDER column which holds one of two values MALE and FEMALE.
Then someone issues the following query:
SELECT * FROM emp WHERE gender 'FEMALE'; If the above query returned approximately 50 of the rows then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its rules said to.
It became obvious that the RBO armed with its set of discrete rules did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table its indexes and the data distribution to make better informed decisions.
Using our previous example assume that the company has employees that are 95 female and 5 male. If you query for females then you do not want to use the index. If you query for males then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.
What is Cost-Based Optimization?
The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it can’t reach do this without good, up-to-date statistical information on the data being accessed. The optimizer can use a rules-based approach to work without statistical information, but this approach is less intelligent than the cost-based approach. With the rules-based approach, the optimizer chooses an execution plan based a set of rules about what types of operations usually execute faster than other types. With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema objects (tables, clusters, or indexes) being accessed.
Rule Based Optimizer Obsolescence
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this osolescence are:
- The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
- The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
- The CHOOSE and RULE optimizer hints still exist but are no longer supported.
- Code requiring the RBO must be migrated to use the query optimizer.
refer from:
http://www.erpgreat.com/oracle-database/rule-based-and-cost-based-optimizer.htm
In this first installment of a two-part article, we will cover the following CBO topics:
Refer from:
http://www.dba-oracle.com/art_otn_cbo.htm
In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored in db.
A long time ago the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically the RBO used a set of rules to determine how to execute a query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example assume someone put an index on the GENDER column which holds one of two values MALE and FEMALE.
Then someone issues the following query:
SELECT * FROM emp WHERE gender 'FEMALE'; If the above query returned approximately 50 of the rows then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its rules said to.
It became obvious that the RBO armed with its set of discrete rules did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table its indexes and the data distribution to make better informed decisions.
Using our previous example assume that the company has employees that are 95 female and 5 male. If you query for females then you do not want to use the index. If you query for males then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.
What is Cost-Based Optimization?
The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it can’t reach do this without good, up-to-date statistical information on the data being accessed. The optimizer can use a rules-based approach to work without statistical information, but this approach is less intelligent than the cost-based approach. With the rules-based approach, the optimizer chooses an execution plan based a set of rules about what types of operations usually execute faster than other types. With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema objects (tables, clusters, or indexes) being accessed.
Rule Based Optimizer Obsolescence
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this osolescence are:
- The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
- The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
- The CHOOSE and RULE optimizer hints still exist but are no longer supported.
- Code requiring the RBO must be migrated to use the query optimizer.
refer from:
http://www.erpgreat.com/oracle-database/rule-based-and-cost-based-optimizer.htm
The goal of SQL tuning is to execute your SQL with the absolute minimum amount of I/O. See my related SQL Optimization tips at the end this article.
Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the challenging job of evaluating any SQL statement and generating the "best" execution plan for the statement.
Because the CBO determines the execution speed for every Oracle query, the Oracle professional must understand how the CBO is influenced by Oracle external issues, internal statistics, and data distribution.In this first installment of a two-part article, we will cover the following CBO topics:
- CBO parameters. We will start by reviewing the basic optimizer modes within the CBO and then drill down and examine specific parameters that influence the behavior of the CBO.
- CBO statistics. We will examine the importance of gathering proper CBO statistics with dbms_stats and review techniques for ensuring that execution plans remain stable. We will also look at techniques for migrating statistics between systems and examine how developers can optimize their
SQL in a test environment and confidently migrate SQL into production without fear of changing execution plans.
Refer from:
http://www.dba-oracle.com/art_otn_cbo.htm
No comments:
Post a Comment