Hi All,

Oracle has added tons of new features into Oracle 23c. They have tried to add lot of cool features into different subjects or areas, Oracle sharding has also got one good addition in the new version. One of the latest sharing related optimizer related parameter added into 23c is OPTIMIZER_CROSS_SHARD_RESILIENCY which enables flexible execution of cross-shard SQL statements.

SQL> select name, value, DESCRIPTION from v$parameter where name like 'optimizer_cross_shard_resiliency';

NAME                                  VALUE        DESCRIPTION
------------------------------------  ------------ ---------------------------------------------------------------- 
optimizer_cross_shard_resiliency      FALSE        enables resilient execution of cross shard queries

As far as a cross-shard statements, It is a query that must scan data from more than one shard, and the processing on each shard is independent of any other shard. A multi-shard query maps to more than one shard and the coordinator might need to do some processing before sending the result to the client. Horizontally scalable cross-shard query coordinators can improve performance and availability of read-intensive cross-shard queries.

Coming back to the parameter, when this parameter is set to TRUE then and a cross-shard query fails on one or more shards, the query execution continues on the Oracle Data Guard standbys of the failed shards, but this should be well tested before implementing in production environment as there might be some performance overhead associated with cross shard resiliency.

For example, in below sharding configuration we have two primary shards and each of them has its READ ONLY standby available in a different zone and shards.

GDSCTL>config shard

Name  Shard Group          Status  State     Region    Availability
----- -------------------- ------- --------- -------- ---------------
sh1   primary_canada_shg   Ok      Deployed  canada   ONLINE
sh2   primary_canada_shg   Ok      Deployed  canada   ONLINE
sh3   standby_india_shg    Ok      Deployed  india    READ ONLY
sh4   standby_india_shg    Ok      Deployed  india    READ ONLY

GDSCTL>

In case when doing a multi-shard query, the query coordinator will assist queries that need data from more than one shard, and in case if any of the primary shard fails to respond, the coordinator will check its DG Standby to furnish the request.

Hope It Helped!
Prashant Dixit