Oracle RAC Sharding for Shard-Friendly Applications

Oracle RAC Sharding improves the scalability and performance of shard-friendly applications running on Oracle Real Application Clusters (Oracle RAC).

A shard-friendly application naturally groups related work by using a unique business key. Oracle RAC Sharding uses this key to establish a key-to-instance mapping and routes connection requests accordingly. This routing reduces inter-instance block transfers and can improve response times and throughput.

Oracle RAC Sharding applies within a single Oracle RAC database. It does not require the entire schema to be partitioned, and it does not require every application module to pass a sharding key. Requests that do not specify a sharding key continue to use the configured load-balancing policy. All tables remain accessible from all Oracle RAC instances. Oracle RAC Sharding affects only how connections are routed when a sharding key is provided.

What Is a Shard-friendly Application?

A shard-friendly application uses a known business key to group related work. The application knows this key before it checks out a database connection, passes the key during connection checkout, and uses the same key in SQL predicates for related work. Common sharding keys include customer IDs, account IDs, tenant IDs, phone numbers, zip codes, country codes, and state codes.

When to Use Oracle RAC Sharding?

Use Oracle RAC Sharding when the application and data model have a natural key that can be used for routing. The feature is a good fit when:

The application knows the sharding key before it checks out a connection.
A frequently accessed table is partitioned by that key.
Most work in a request or transaction uses the same key value.
SQL statements for the unit of work include the key in their predicates.
The key distributes data and workload across partitions.
The application can pass the key when requesting a pooled connection.

Oracle RAC Sharding is less suitable when the key is not known until after the connection is established, when most transactions access many unrelated key values, or when the selected key creates a highly skewed workload.

Choosing a Sharding Key

Choose the sharding key from the application access pattern. Start with the most frequently accessed tables and identify the business key that the application already uses to locate or group data.

A good sharding key is stable, known before connection checkout, included in SQL predicates, and able to distribute data and work across partitions. If several frequently accessed tables use the same key and the same partitioning strategy, designate the most frequently accessed partitioned table for Oracle RAC Sharding..

Enabling Oracle RAC Sharding

Oracle RAC Sharding is supported for partitioned tables. After you select the partitioned table that represents the sharding key, enable Oracle RAC Sharding for that table. For example:

ALTER SYSTEM ENABLE AFFINITY inventories;

Make an Application Shard-Friendly

To use Oracle RAC Sharding, the application must pass the sharding key when it requests a connection from the connection pool. Without Oracle RAC Sharding, the application might request a connection without a key. With Oracle RAC Sharding, the application builds a sharding key and passes it when requesting the connection:

OracleShardingKey shardingKey = pds.createShardingKeyBuilder()
    .subkey(country_code, OracleType.VARCHAR2)
    .build();

Connection connection = pds.createConnectionBuilder()
    .shardingKey(shardingKey)
    .build();

After the application obtains the connection, it should use the same key in the SQL predicates for that unit of work. For example, if COUNTRY_CODE is the sharding key, include COUNTRY_CODE in the predicate:

UPDATE inventories
   SET quantity_on_hand = quantity_on_hand - :quantity
 WHERE product_id = :product_id
   AND warehouse_id = :warehouse_id
   AND country_code = :country_code;

Application changes can be limited to the modules that benefit from key-based routing. Modules that do not have a suitable sharding key can continue to request connections without specifying one.

If the Application is not Already Shard-Friendly

If the application is not already shard-friendly, use these steps to evaluate and prepare it:

Identify the most frequently accessed tables in the workload.
Determine whether those tables are commonly accessed by a stable business key.
Partition the selected hot table by that key, if it is not already partitioned.
Confirm that the key provides a balanced distribution of data and workload.
Update connection checkout logic to pass the sharding key.
Update the related SQL statements so that they include the same key in their predicates.
Leave non-shard-friendly modules unchanged unless they can benefit from key-based routing.