3.4 Optimizer

---

Oracle optimizer is a critical piece of the architecture and is an important concept that should be understood before going forward. Oracle has two types of optimizer engines, the rule-based optimizer (RBO) and the cost- based optimizer (CBO). From the beginning the RBO has been in use through all versions of Oracle. Under this method Oracle determined the execution path for queries based on a set of predefined rules. That is, if a specific rule was met the execution path was determined based on the set rule.

In Version 7.0, Oracle introduced the CBO. Under this option, the method used to determine the execution path was based on the actual cost of executing the query. To determine the cost, Oracle tried various methods of executing the query on certain statistics that were provided, hence CBO took a mathematical approach to generate the execution plan. Instead of any predefined rules, Oracle uses a certain set of formulas to calculate the cost of an SQL statement. The CBO generates various permutation combinations of execution plans and selects the execution path that has the lowest cost.

Note 

Starting with Version 8.0, Oracle has stopped enhancements to the RBO; the RBO remains a feature in the recent versions of Oracle only as a means to encourage users to migrate to the CBO.

Since a mathematical approach is based on data, if sufficient or correct data is not provided, the optimizer does not compute the correct expected result set and hence generates an execution path that is not efficient enough. This happens when no statistics have been gathered or the characteristics of the objects have changed significantly. Statistics are collected using certain built-in packages such as ANALYZE and DBMS_STATS. While ANALYZE was the earlier method to compute statistics, the DBMS_STATS package is a more recent package and provides even better statistics, making the CBO even more efficient.

On large systems, computation of statistics every time with the change in the characteristics of the objects became an expensive process. In order to make this less expensive, in Version 9i Oracle introduced the MONITORING option, where statistics were collected in smaller increments and the data was merged to the main collection at user defined intervals.

Oracle 9i 

New Feature: In Version 9i Oracle has introduced a MONITORING clause for the CREATE OR ALTER TABLE statement that tracks the approximate INSERT, UPDATE or DELETE operations against the table since the last time statistics were gathered.

Oracle 10g 

Depreciated Feature: Starting with Oracle 10g RBO is deprecated. The only optimizer supported would be the CBO. Along with the deprecation of RBO, the 'RULE' and 'CHOOSE' options used with the OPTIMIZER_MODE parameter has also been deprecated. The only supported options are FIRST_ROWS, FIRST_ROWS_n and ALL_ROWS.

The following is an example to compute statistics using the DBMS_STATS package:

DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME =>MVALLATH, granularity =>ALL, cascade => TRUE);

The above package will compute statistics for schema owner MVALLATH with a granularity level of ALL and cascade option to TRUE.

---