How to set optimizer_mode parameters in Oracle

This article introduces how to set optimizer_mode parameters in Oracle, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

When Oracle executes SQL statements, there are two optimization methods: rule-based RBO and cost-based CBO. When SQL teaches, what kind of optimization method is used is determined by the Oracle parameter optimizer_mode.

Query command

SQL > show parameter optimizer_mode

NAME TYPE VALUE

-

Optimizer_mode string ALL_ROWS

There are four optimizer_mode parameter values:

First: CHOOSE

This is the default value for Oracle. When using this value, Oracle can use either rule-based RBO or cost-based CBO, which value depends on whether statistics are available in the accessed table of the current SQL.

If there are multiple accessed tables, one or more of them have statistics, then Oralce will sample the tables without statistics (that is, not all of them), and then use the cost-based optimization method CBO when the statistics are complete.

If all the accessed tables do not have statistics, Oracle uses the rule-based optimization method RBO.

Second: ALL_ROWS

Whether there is statistical information or not, all adopt the cost-based optimization method CBO.

Third: FIRST_ROWS_n

The n value after FIRST_ROWS_n can be 1Power100 and 1000, whether there is statistical information or not, all adopt the cost-based optimization method CBO, and return the first N rows of records as quickly as possible.

Fourth: FIRST_ROWS

Use a combination of cost and heuristics 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 just the opposite of ALL_ROWS, which uses a rule-based optimization method whether it is statistical or not.

How to change the parameters of optimizer_mode:

Alter session set optimizer_mode='RULE'

On how to set optimizer_mode parameters in Oracle to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.