Oracle教程 2023-02-26

Oracle 参数 OPTIMIZER_MODE 官方解释，作用，如何配置最优化建议

参数

OPTIMIZER_MODE参数可以调整Oracle数据库的优化器模式。它有三种模式： ALL_ROWS（默认模式，优化器试图选择最优查询计划来最小化总体查询时间）；FIRST_ROWS（优化器试图索取最早可得到的行）；RULE（基于rule-based优化器）。

正确设置Oracle参数的步骤：

1、首先，在SQL * Plus使用DBA角色登录数据库。

2、查询OPTIMIZER_MODE参数的值： SELECT name, value FROM v$parameter where name = ‘optimizer_mode’;

3、修改OPTIMIZER_MODE参数： ALTER SYSTEM SET OPTIMIZER_MODE = all_rows;

4、提交修改： COMMIT;

5、再次查看OPTIMIZER_MODE参数的值： SELECT name, value FROM v$parameter where name = ‘optimizer_mode’;

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

Property	Description
Parameter type	String
Syntax	`OPTIMIZER_MODE =` `{ FIRST_ROWS_[1 \| 10 \| 100 \| 1000] \| FIRST_ROWS \| ALL_ROWS }`
Default value	`ALL_ROWS`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Modifiable in a PDB	Yes
Basic	No

Values

FIRST_ROWS_n

The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
FIRST_ROWS

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
ALL_ROWS

The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).