The Optimizer

A optimizers is the part of an ORACLE kernel that chooses the best way to use the tables and indexes to complete the request made by a SOL statement.

Optimization is an important step in the processing of any Data Manipulation Language statement (SELECT, INSERT UPDATE or DELETE). Whenever such a statement is issued, ORACLE must determine how to execute the statement. There may be many different ways for ORACLE to execute a single statement. A part of ORACLE called the optimizer choose one of these ways.

The goal of the optimizer is to choose the most efficient way to execute a SOL statement. The optimizer considers a number of factors to make what is usually the best choice among its alternatives. However, an application designer usually knows more about a particular application's data than the optimizer could possibly know.

Why use the Optimizer?

For every SQL statement, the Oracle optimizer generates an execution plan, which is a serious of steps that Oracle takes to execute the statement. Because you know more about the application data than the optimizer does, you can suggest to Oracle the way to optimize a SQL statement.

Oracle's Approaches to Optimization

To choose an execution plan for a SQL statement, the optimizer uses one of these approaches:

1. Rule-based

Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available. This approach chooses execution plans based 'on heuristically-ranked operations. If there is more than one way to execute a SQL statement, the rule-based approach always uses the operation with the lower rank. In most cases, operations of lower rank execute faster than those associated with constructs of higher rank.
2. Cost-based

This approach consists of three steps:

1. Using the cost-based approach, the optimizer generates a set of potential execution plans for the statement based on its available access paths and hints. The optimizer estimates the cost of each execution plan based on the data distribution and storage characteristics for the tables, Clusters, and indexes in the data dictionary.

2. The cost is the estimated value proportional to the elapsed time needed to execute the statement using the execution plan. The optimizer calculates the cost based on the estimated computer resources including but not limited to

1/0, CPU time, and memory required to execute the statement using the plan. Execution plans with greater costs take more time to execute than those with smaller costs.

3. The optimizer compares the costs of the execution plans and chooses the one with the smallest cost.

Goal of the Cost-Based Approach

By default, the goal of ' the cost-based approach is the best throughput, or minimal elapsed time necessary to process all rows accessed by the statement.

Domain Name Search