Lesson 5

In the early 2000s, particularly with Oracle 9i released in 2001, two primary optimizer modes were dominant: Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO). The RBO was the older of the two, and it relied on a fixed set of rules to determine the execution plan of a SQL query. The CBO, introduced in Oracle 7, used a more sophisticated approach. Instead of relying on predefined rules, it leveraged statistics on the data distribution and storage characteristics of tables and indexes to make educated guesses on the most efficient execution plan.
By Oracle 9i, RBO was being phased out, with CBO considered the superior choice. The CBO offered four modes: ALL_ROWS, FIRST_ROWS, FIRST_ROWS_n, and RULE.

With the advent of Oracle 10g in 2003, the optimizer saw significant improvements. The CBO became the default, and Oracle stopped supporting RBO in later versions. The optimization modes available remained mostly the same, but the optimizer itself became more sophisticated. The statistics collection was automated, and new features such as SQL Tuning Advisor and SQL Access Advisor were added, which used the CBO to provide tuning recommendations.

Released in 2007, Oracle 11g carried forward the CBO's legacy while introducing enhancements like Automatic Memory Management and SQL Plan Management. These features added more intelligence to the optimizer, helping it to improve SQL performance based on changing conditions in the database.

Oracle 12c, introduced in 2013, marked a significant shift in the optimizer's capabilities with the launch of Adaptive Query Optimization. This feature allowed the optimizer to make runtime adjustments to the execution plan based on the actual data processed.

In Oracle Database 18c and 19c, the optimizer evolved further, with machine learning being integrated into its functionality. Automatic Indexing was introduced, enabling the database to automatically create, test, validate, and maintain indexes for improved SQL performance.

The release of Oracle 21c in 2020 took the optimizer to another level with Automatic In-Memory (AIM). This feature leveraged machine learning to manage the In-Memory column store, automatically moving objects in and out based on usage patterns.

By 2022, the optimizer modes remained mostly the same as they were in the Oracle 9i era. However, the sophistication and intelligence behind the scenes had greatly evolved. The most common optimizer modes were ALL_ROWS, which targets best throughput, and FIRST_ROWS_n, which prioritizes the fastest retrieval of the first 'n' rows.

In conclusion, while the fundamental optimizer modes in Oracle remained largely constant from 2003 to 2022, the underlying optimizer saw radical transformations. From simple rule-based and cost-based systems to intelligent, adaptive, and automated functionalities, Oracle's optimizer has continued to evolve, ensuring that Oracle databases remain performant and efficient.

We have now covered the basic principles behind the default optimizer modes for Oracle and we are now ready to delve deeper into the characteristics of each.
You should now be able to:

1. Describe each optimizer mode
2. View and reset the default optimizer mode
3. Override the default optimizer mode for a query

Here are the terms from this module that may have been new to you:

1. all_rows hint: The default cost-based optimizer method, all_rows maximizes overall execution time.
2. Choose optimizer: This is a default optimizer_mode in the init.ora file. If statistics exist for any table in a query, CHOOSR will invoke the cost-based optimizer.
3. Cost-based optimizer: This is the latest SQL optimizer that uses object statistics to make intelligent table access decisions.
4. Execution plan: This is the compiled execution path to Oracle data.
5. first_rows hint: This is a cost-based SQL optimizer hint that delivers rows as quickly as possible.
6. Optimizer mode: This is the default optimizer as set in the init.ora file.
7. Rule-based optimizer: This is the first Oracle SQL optimizer; it uses general rules to formulate execution plans.

You will explore the rule-based optimizer.

Click the Quiz link below to take a quiz and test your knowledge of the concepts in this module.
SQL Optimizer Mode Defaults- Quiz