SQL Tuning   «Prev  Next»

Lesson 6The SQL optimizer modes
ObjectiveDifference between the rule-based and cost-based Optimizers

SQL Optimizer Modes

Rule-based optimizer

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.

Cost-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.

Using Rule-Based Optimization

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.