SQL Optimizer Modes

Oracle databases utilize two primary types of query optimizers: the Rule-Based Optimizer (RBO) and the Cost-Based Optimizer (CBO). These tools are designed to determine the most efficient way to carry out SQL statements. The core difference between these two optimizers is the approach they use to evaluate the execution plan for a given query.

1. Rule-Based Optimizer (RBO): The Rule-Based Optimizer, as its name implies, makes decisions based on a predefined set of rules. These rules rank the available access paths and join orders, and the optimizer chooses the path with the highest rank. The rules are applied without consideration for the actual data distribution or the current database state. While the RBO was effective for earlier, simpler databases, it was discontinued after Oracle 10g due to its limitations handling complex queries and databases.
2. Cost-Based Optimizer (CBO): Unlike the RBO, the Cost-Based Optimizer takes a more data-driven approach to SQL optimization. It uses statistics about the data stored in the database, such as data distribution, storage indexes, and the database's current state, to estimate the "cost" of potential execution plans. Costs relate to factors such as I/O, CPU usage, and network latency. The CBO selects the execution plan with the lowest estimated cost, which it predicts will be the fastest and most efficient. This makes the CBO much more adaptable and capable in handling complex queries and large databases.

It's important to note that, as of Oracle Database 10g, Oracle Corporation has stopped supporting the RBO. The CBO is now the primary query optimizer for Oracle databases. For optimal performance, Oracle recommends gathering statistics on all database objects, which is necessary for the CBO to make informed decisions about the execution plan for a SQL statement. In summary, while both RBO and CBO are SQL optimizers used in Oracle databases, they operate on fundamentally different principles. RBO uses a static set of rules to determine the execution plan, while CBO estimates the cost of different execution plans based on database statistics and chooses the least costly one, providing more efficient and adaptable optimization.

1) Rule-based Optimizer (RBO) has been deprecated in Oracle Database. Although its functionalities still exist in older versions, it will be entirely removed in a future release. Oracle strongly advises using the Cost-based Optimizer (CBO) as the preferred method for query optimization. Here's a breakdown: Rule-based Optimizer (RBO):

• Employs predefined rules to choose the execution plan for a query.
• Less flexible and adaptable than CBO.
• Can be susceptible to performance problems in complex queries.
• Deprecated and scheduled for removal in future releases.

Cost-based Optimizer (CBO):

• Analyzes query characteristics and data statistics to estimate the cost of different execution plans.
• Chooses the plan with the lowest estimated cost.
• More flexible and adaptable than RBO.
• Generally leads to better performance for complex queries.
• Recommended by Oracle for optimal query optimization.

Therefore, for future-proofing your applications and ensuring optimal performance, relying on the CBO is the safe and preferred option.

The first Oracle optimizer, which is the part of Oracle that attempts to determine the most efficient way to execute your SQL code, was developed in the mid-1980s when Oracle was still a small and struggling database vendor. At that time, the best way to develop an execution plan was to look at the structure of the tables and indexes and develop an execution plan based upon the structure of the database. This process was known as the rule-based optimizer.

To remedy these types of issues, Oracle began development of a cost-based optimizer. The cost-based optimizer relies on the data dictionary being populated with information about the nature and distribution of data (called statistics). Rather than relying on generic rules, the cost-based optimizer will make access decisions based upon the true nature of the data.
We will now take a closer look at the rule-based optimizer.

Rule-based optimization is supported in Oracle8, but you are advised to write any new applications using cost-based optimization. Cost-based optimization should be used for new applications and for data warehousing applications, because it supports new and enhanced features. Much of the functionality in Oracle8 (such as hash joins, improved star query processing, and histograms) is available only through cost-based optimization. If you have developed existing OLTP applications using version 6 of Oracle and have tuned your SQL statements carefully based on the rules of the optimizer, you may want to continue using rule-based optimization when you upgrade these applications to Oracle8.
If you neither collect statistics nor add hints to your SQL statements, your statements will use rule-based optimization. However, you should eventually migrate your existing applications to use the cost-based approach, because the rule-based approach will not be available in future versions of Oracle. If you are using an application provided by a third-party vendor, check with the vendor to determine which type of optimization is best suited to that application.
You can enable cost-based optimization on a trial basis simply by collecting statistics. You can then return to rule-based optimization by deleting them or by setting either the value of the OPTIMIZER_MODE initialization parameter or the OPTIMIZER_MODE option of the ALTER SESSION command to RULE. You can also use this value if you want to collect and examine statistics for your data without using the cost-based approach.