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 |
|
|
Default value |
If If |
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Basic |
No |
|
Oracle RAC |
Multiple instances can have different values. |
Values
-
falseOracle does not use rewrite.
-
trueOracle costs the query with rewrite and without rewrite and chooses the method with the lower cost.
-
forceOracle always uses rewrite and does not evaluate the cost before doing so. Use
forcewhen 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
