Oracle 参数 OPTIMIZER_CAPTURE_SQL_QUARANTINE 官方解释，作用，如何配置最优化建议
本站中文解释
OPTIMIZER_CAPTURE_SQL_QUARANTINE参数是Oracle数据库 11gR2中引入的新参数，用于控制Oracle优化器保存未编译执行计划的SQL。当设置为TRUE时，Oracle会自动捕获这些未编译的SQL，将其保存在SQL Quarantine Area（SQL隔离区）中，并在未来将它们重新用最佳计划来编译。
正确设置该参数的方法：
1. 使用SQL*Plus登录到数据库实例；
2. 授予上述权限。语句如下：
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_QUARANTINE=TRUE;
3. 让变化立即生效。语句如下：
ALTER SYSTEM FLUSH SHARED_POOL;
4. 配置该参数会自动生成一个名为SQLQ_TUNE_INSTANCE_PARAMETERS的编译器参数，可以更详细地设置参数，避免将无用的SQL保存到隔离区。
官方英文解释
OPTIMIZER_CAPTURE_SQL_QUARANTINE enables or disables the automatic creation of SQL Quarantine configurations.
| Property | Description |
|---|---|
|
Parameter type |
Boolean |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
|
Oracle RAC |
Different instances can use different values. |
Values:
-
trueEnables the automatic creation of SQL Quarantine configurations. If the Resource Manager terminates a SQL statement because the statement has exceeded resource limits, then the database automatically creates a SQL Quarantine configuration for the execution plan used by the terminated SQL statement. Note that the plan for a terminated SQL statement is quarantined, not the statement itself.
-
falseDisables the automatic creation of SQL Quarantine configurations.
Note:
This parameter is available starting with Oracle Database 21c.
See Also:
-
“DBA_SQL_QUARANTINE”
-
“OPTIMIZER_USE_SQL_QUARANTINE”
-
Oracle Database SQL Tuning
Guide for more information about SQL Quarantine
