MULTISHARD_QUERY_DATA_CONSISTENCY

MULTISHARD_QUERY_DATA_CONSISTENCY enables you to specify a data consistency setting for multi-shard queries.

Property	Description
Parameter type	String
Syntax	MULTISHARD_QUERY_DATA_CONSISTENCY = { STRONG | SHARD_LOCAL | DELAYED_STANDBY_ALLOWED | STANDBY_ONLY | STANDBY_PREFERRED | LOCAL_STANDBY_ONLY | LOCAL_STANDBY_PREFERRED }
Default value	STRONG
Modifiable	ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB	Yes
Basic	No
Oracle RAC	Different instances can use different values.

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: 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: SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting provides the most current data.

• DELAYED_STANDBY_ALLOWED: 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.

• STANDBY_ONLY: SCN synchronization is not performed across all shards. Data is consistent within each shard. Data is fetched only from standbys. If there are multiple standbys, chunks are distributed equally among them. If no standbys exist or none are alive, an error is raised.

• STANDBY_PREFERRED: SCN synchronization is not performed across all shards. Data is consistent within each shard. Data is fetched from the standbys when they are available, that is, the primary shard is excluded. If there are multiple standbys, chunks are distributed equally among them. If there is no standby to serve the request, data is fetched from the primary shard.

• LOCAL_STANDBY_ONLY: SCN synchronization is not performed across all shards. Data is consistent within each shard. This setting supports region affinity for cross-shard queries. It allows data to be fetched from standby shards in the same region as the coordinator running the query. If there are no standby shards available, an error is raised.

• LOCAL_STANDBY_PREFERRED: This setting is similar to the LOCAL_STANDBY_ONLY setting, except the database attempts to find a shard from which to fetch data in the following order:

  1. Standby shards in the same region; chunks distributed equally among shards

  2. Primary shard in the same region

  3. Standby shards in the buddy region; chunks distributed equally among shards

  4. Primary shard in the buddy region

  5. If no shards are available, raise an error.

Note: The LOCAL_STANDBY_ONLY and LOCAL_STANDBY_PREFERRED settings are available starting with Oracle AI Database 26ai, Release Update 23.9.

See Also: Oracle Globally Distributed AI Database for more information about specifying consistency levels in a multi-shard query