Overview of the Optimizer in oracle 19c and Types of Optimizer

In Oracle 19c, the optimizer plays a crucial role in optimizing SQL statements. The optimizer uses various strategies to transform the SQL statements into an optimal plan, thereby minimizing the overall cost of execution.

In this blog, we will provide an overview of the optimizer in Oracle 19c.

The Optimizer in Oracle 19c:

The optimizer is responsible for generating an efficient execution plan for a given SQL statement. The execution plan consists of various operations, such as joins, scans, and aggregations, that determine the sequence of operations required to retrieve the desired result set.

Cost-Based Optimization:

Oracle 19c utilizes a cost-based optimization (CBO) technique to select the most efficient execution plan. The CBO evaluates the cost of different plans based on factors such as table statistics, indexes, and system configuration. By comparing the costs, the optimizer can determine the optimal plan that will provide the best performance for a given SQL statement.

Caching and Reusing Execution Plans:

Oracle 19c employs various techniques to improve query performance by caching and reusing execution plans. For example, Oracle can store a previously generated execution plan for a given SQL statement and reuse it when the same SQL statement is executed again. This technique reduces the overhead of plan generation and execution.

Parsing, Translation, and Execution:

The optimizer follows a series of steps during the parsing, translation, and execution of a SQL statement. These steps include parsing, which converts the SQL statement into an internal format; translation, which generates the execution plan based on the cost-based optimization techniques; and execution, which carries out the plan to retrieve the desired result set.

Advanced Optimization Techniques:

Oracle 19c provides several advanced optimization techniques, such as dynamic sampling, cardinality feedback, and adaptive execution plans. These techniques enhance the optimizer's ability to generate efficient execution plans for complex SQL statements.

Types of Optimizers-

Oracle 19c has several kinds of Optimizers.

Rule-Based Optimizer (RBO)

The Rule-Based Optimizer (RBO) in Oracle 19c is responsible for performing transformations on the SQL statement. It follows a series of rewrite rules to transform the SQL statement into a more efficient form. However, RBO does not use any statistics or cost-based optimization techniques.

Cost-Based Optimizer (CBO)

The Cost-Based Optimizer (CBO) in Oracle 19c generates the execution plan for a given SQL statement based on cost-based optimization techniques. It evaluates the cost of different plans by considering factors such as table statistics, indexes, and system configuration. By comparing the costs, CBO can determine the optimal plan that will provide the best performance for a given SQL statement.

Adaptive Optimizer (Adaptive CBO)

The Adaptive Optimizer (Adaptive CBO) in Oracle 19c is an enhanced version of the Cost-Based Optimizer (CBO). It incorporates additional features such as dynamic sampling, cardinality feedback, and adaptive execution plans to enhance the optimizer's ability to generate efficient execution plans for complex SQL statements.

Transparent Query Execution Optimizer (Transparent CBO)

The Transparent Query Execution Optimizer (Transparent CBO) in Oracle 19c is designed to optimize SQL statements that involve materialized views or other types of optimization views. It generates an execution plan for the SQL statement based on the properties of the optimization views and considers factors such as query transformation, statistics, and system configuration.

Hint-Based Optimizer

The Hint-Based Optimizer in Oracle 19c allows users to override the default optimizer decisions and enforce specific optimization techniques or strategies. It enables users to specify hints within the SQL statement, which instruct the optimizer to follow a specific optimization path.

In summary, Oracle 19c offers a comprehensive set of optimizer types, each with its own set of capabilities and optimization techniques. These optimizers work together to generate efficient execution plans for complex SQL statements, thereby enhancing the overall performance and scalability of Oracle 19c data management and analytics applications.