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

本站中文解释

QUERY_REWRITE_ENABLED参数控制是否启用查询重写功能,默认是TRUE,即启用查询重写功能,也就是把查询转换成 Oracle 数据库将更易于执行的查询。当该参数设置为FALSE时,即不启用查询重写功能。

查询重写的目标是减少查询期间并行化操作数,让系统更有效地使用并行处理。

正确设置:

1、检查数据库是否需要这个参数,主要考虑以下因素:数据库是否启用了查询重写,是否有高度负载的业务;
2、设置QUERY_REWRITE_ENABLED参数,配置查询重写;
3、测试查询重写是否生效,可以查询V$SQL_REWRITE视图;
4、更新更改后的参数;
5、重新启动服务器。

官方英文解释

Use QUERY_REWRITE_ENABLED to enable or disable query rewriting globally for the database.

Property Description

Parameter type

String

Syntax

QUERY_REWRITE_ENABLED = { false | true | force }

Default value

If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then true

If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0 or lower, then false

Modifiable

ALTER SESSION, ALTER SYSTEM

Modifiable in a PDB

Yes

Basic

No

Oracle RAC

Multiple instances can have different values.

Values

  • false

    Oracle does not use rewrite.

  • true

    Oracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.

  • force

    Oracle always uses rewrite and does not evaluate the cost before doing so. Use force when you know that the query will always benefit from rewrite and when reduction in compile time is important.

To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.

See Also:

  • Oracle Database Data
    Warehousing Guide
    for information on query rewrite of materialized views

  • Oracle Database SQL Tuning
    Guide
    and “OPTIMIZER_MODE” for information on cost-based optimization


数据运维技术 » Oracle 参数 QUERY_REWRITE_ENABLED 官方解释,作用,如何配置最优化建议