Oracle 参数 MULTISHARD_QUERY_DATA_CONSISTENCY 官方解释，作用，如何配置最优化建议
本站中文解释
本参数用于控制多副本查询的数据一致性，注意：该参数只对只针对单表的单块表读取查询（无聚合函数）有效。
该参数的可选值为STRICT和ALLOWED：
1. STRICT：当一次查询跨越多个副本集块(RAC Blocks)时，在一次查询的执行过程中副本集块的数据必须是一致的，我们就叫做STRICT模式。在STRICT模式中，查询若发现有一个RAC Block上的数据与另一个RAC Block上的数据不一致，这个查询就会失败，使得数据总是可以保持一致性。
2. ALLOWED：在ALLOWED模式中，当一次查询跨越多个RAC Blocks时，这个查询会继续执行，但可能会返回不一致的结果，用户可以手动构建一个视图，来实现对不一致数据过滤等操作。
正确设置:
对于多副本查询，建议将MULTISHARD_QUERY_DATA_CONSISTENCY参数值设置为STRICT，以保证查询结果的数据一致性。通过SQL> alter system set multishard_query_data_consistency=strict scope=both; 命令设置。
官方英文解释
MULTISHARD_QUERY_DATA_CONSISTENCY enables you to specify a data consistency setting for multi-shard queries.
| Property | Description |
|---|---|
|
Parameter type |
String |
|
Syntax |
|
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Basic |
No |
|
Oracle RAC |
The value of this parameter can be different on different Oracle RAC instances. |
You can use MULTISHARD_QUERY_DATA_CONSISTENCY to avoid the cost of SCN synchronization when executing multi-shard queries across shards, which can be globally distributed.
MULTISHARD_QUERY_DATA_CONSISTENCY can be set at the system level or the session level.
The values that can be set for MULTISHARD_QUERY_DATA_CONSISTENCY are:
-
STRONG: With this setting, SCN synchronization is performed across all shards, and data is consistent across all shards. This setting provides global consistent read capability. This is the default value. -
SHARD_LOCAL: With this setting, SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting provides the most current data. -
DELAYED_STANDBY_ALLOWED: With this setting, SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting allows data to be fetched from Data Guard standby databases when possible (for example, depending on load balancing), and may return stale data from standby databases.
See Also:
Using
Oracle Sharding for more information about specifying consistency levels in a multi-shard query
