An Oracle Remote Database Administrator (DBA) may need to look at the default optimizer mode of their database in various circumstances. The default optimizer mode governs the general behavior of the Oracle SQL optimizer, a component that selects the most efficient execution plan for SQL statements. The two main modes are ALL_ROWS and FIRST_ROWS_n, and they impact how the Cost-Based Optimizer (CBO) optimizes queries.
- Performance Tuning: If the DBA is conducting performance tuning activities, examining the default optimizer mode can provide important context. It can help understand the nature of any performance issues that might be observed, as different optimizer modes have different optimization strategies that can impact SQL performance.
- Change in Workload Characteristics: If the workload characteristics of the database change, for example, transitioning from an OLTP (Online Transaction Processing) system to a DSS (Decision Support System) or vice versa, the DBA should re-evaluate the optimizer mode. ALL_ROWS mode is typically more suitable for DSS workloads that prioritize overall throughput, whereas FIRST_ROWS_n mode can be more appropriate for OLTP systems where response time for individual transactions is more important.
- Upgrades and Migration: During Oracle upgrades or migration projects, the DBA should check the default optimizer mode to ensure it is set appropriately for the nature of the workload on the new version of the database.
- SQL Tuning: When carrying out specific SQL tuning tasks, understanding the optimizer mode is crucial. The DBA can then see if changes to the mode may improve the performance of specific problem SQL statements.
- Investigating Inconsistent Query Performance: If there are cases where a query's performance is inconsistent, checking the default optimizer mode can provide insights. For example, if the optimizer mode has been manually overridden at the session level or within specific SQL statements, this could lead to inconsistencies in performance.
The default optimizer mode is a key configuration setting that governs the behavior of Oracle's SQL optimizer. An Oracle Remote DBA needs to be aware of this setting during various activities such as performance tuning, workload changes, upgrades, SQL tuning, and performance troubleshooting.
One of the first things the Oracle Remote DBA looks at is the
default optimizer mode for their database.
There are two classes of optimizer modes:
- (RBO) the rule-based optimizer and
- (CBO) the cost-based optimizer.
The Oracle init.ora parameters offer four values for the optimizer_mode parameter.
optimizer_mode = RULE
The first, and oldest optimizer mode is RULE. Under the rule-based optimizer, Oracle uses heuristics from the data dictionary in order to determine the most effective way to service an Oracle query and translate the declarative SQL command into an actual navigation plan to extract the data. In many pre-Oracle8i systems, rule-based optimization is faster than cost-based. In fact, Oracle Applications used rule-based optimization until release 11i.
optimizer_mode = FIRST_ROWS
This is a cost-based optimizer mode that will return rows as soon as possible, even if the overall query runs longer or consumes more resources. The FIRST_ROWS optimizer mode usually involves choosing a full index scan over a parallel full table scan. Because the FIRST_ROWS mode favors index scans over full table scans, the FIRST_ROWS mode is most appropriate for inline systems where an end user wants to see some results as quickly as possible.
optimizer_mode = ALL_ROWS
This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full table scan over a full index scan. Because the ALL_ROWS mode favors full table scans, the ALL_ROWS mode is best suited for batch-oriented queries where intermediate rows are not required for viewing.
To illustrate the difference between ALL_ROWS and FIRST_ROWS with an oversimplistic example, consider the following query:
