Meaning of using the optimizer_mode parameter in Oracle

Meaning of using the optimizer_mode parameter in Oracle

Oracle uses the Optimizer_mode parameter to control the optimizer preferences. 9i has several common parameters: first_rows, all_rows, first_rows_N, rule, and choose. 10 Gb is less than rule and choose. Oracle has two optimization methods when executing SQL statements: rule-Based RBO and cost-based CBO. The Oracle optimizer_mode parameter determines the optimization method used during SQL Execution.

Rule Based Optimizer (RBO) Based on Rules
Cost Based Optimizer (CBO) Based on Cost or statistics

SQL> show parameter optimizer_mode;

NAME TYPE VALUE

Optimizer_mode string ALL_ROWS

Oracle 10g starts with all_rows by default.

No matter whether statistical information exists or not, ALL_ROWS uses cost-based optimization methods to return the maximum amount of data.

No matter whether there is statistical information, FIRST_ROWS_n adopts cost-based optimization methods and returns the first N rows of records at the fastest speed.

FIRST_ROWS uses the combination of cost and test method to find a method that can return the first few rows as quickly as possible.

The first is the minimum cost of returning all rows, the second is the minimum cost of returning the first N rows, and the last is the best plan to return the first few rows

Modify global
Alter system set optimizer_mode = all_rows;

Modify current session

Alter session set optimizer_mode = all_rows;

Specific explanations of several parameters:

Optimizer_mode = choose
This is the default value of Oracle. When this value is used, Oracle can adopt a rule-based RBO or a cost-based CBO to determine which value to use, it depends on whether the currently accessed SQL table has available statistics. If there are multiple accessed tables, one or more of which have statistical information, Oralce will perform sampling statistics (I .e. not all sampling) on the tables without statistical information ), after the statistics are complete, use the cost-based optimization method CBO. If no statistical information is available for all accessed tables, Oracle uses the rule-based optimization method RBO.

Optimizer_mode = First_rows
This option is outdated after oracle 9i and is retained for backward compatibility purposes, this option is used to find the execution plan of the first row that can return the result set in the shortest time. This rule tends to encourage the optimizer to use the index access path, and occasionally there will be very inappropriate access paths.

After the CBO mode is set, SQL statements return results as quickly as possible, regardless of whether all the system queries take a long time or consume too much system resources. Because the use of indexes will speed up the query, the first_rows optimization mode performs index scanning on the full table scan. This optimization mode is generally suitable for some OLTP systems to meet the requirements for users to see smaller query result sets in a short period of time.

Optimizer_mode = all_rows
The optimizer looks for execution plans that can complete statements in the shortest time.
When this CBO mode is set, the minimum amount of computing resources consumed is guaranteed, even if no results are returned after the query ends. The optimization mode of all_rows is more inclined to full table scanning, rather than full index scanning and index sorting, therefore, this optimization mode is suitable for data warehouses, decision support systems, and batch-oriented databases that are not so real-time.

Optimizer_mode = first_rows_N
The N value can be,. the optimizer first thoroughly analyzes the first connection order to estimate the total number of returned rows. In this way, you can know the fragments of the entire dataset that you may obtain and restart the optimization process. The goal is to find the execution plan that can return the entire data segment with minimal resource consumption.

Oracle 9i strengthens the SQL statement optimization mode with a small amount of data in the expected returned result set, and adds four parameter values: first_rows_1, first_rows_10, first_rows_100, and first_rows_1000. CBO determines the base number of returned result sets by using the n value in first_rows_n. We may only need to query a part of the result set. CBO determines whether to use index scanning based on the n value.

Optimizer_mode = rule
The rule-based optimizer mode, RBO, is an optimization mode used in earlier Oracle versions. RBO does not support new features of Oracle in 1994, such as bitmap indexes, table partitions, and function-based indexes. Therefore, RBO will not be updated in later Oracle versions, it is not recommended that you use the RBO optimization mode.