Introduction to Oracle optimization parameters optimizer_mode

Oracle has two optimization methods when executing SQL statements: Rule-Based RBO and cost-based CBO. The Oracle parameter optimizer_mode determines the optimization method used during SQL coaching.

SQL> show parameter optimizer_mode

Name type value
-----------------------------------------------------------------------------
Optimizer_mode string choose

Optimizer_mode has the following four values:

First: 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.

Second: all_rows

Whether there is statistical information or not, cost-based optimization methods are adopted CBO.

Third: first_rows_n

No matter whether statistics are available or not, cost-based optimization methods CBO are used to return the first n rows of records at the fastest speed.

Fourth: first_rows

The cost and test method are used to find a method that can return the first few rows as quickly as possible. This parameter is mainly used for backward compatibility.

Fifth: Rule

This parameter is exactly the opposite of all_rows. No matter whether it is statistical information or not, all use the rule-based optimization method.

How can I change the optimizer_mode parameter? You can use the following methods.

SQL> alter session set optimizer_mode = 'rule ';

The session has been changed.