Oracle RAC enables applications to scale across multiple database instances while providing high availability and transparent access to data. For applications with key-based access patterns, RAC routes connections by establishing affinity between the application sharding key and the RAC instance associated with the corresponding data. Many OLTP applications naturally access data using a business or application key, such as a customer ID, account ID, tenant ID, or country code. Applications that provide this key when requesting a connection can be routed to the RAC instance associated with the corresponding data. By bringing connections closer to the data they access, RAC sharding improves cache locality, reduces inter-instance traffic, and can improve application performance and scalability. 

What is RAC sharding and how does this work

RAC sharding is a RAC optimization to use the application-provided sharding key to establish affinity between connections and instance where the data is cached. A sharding key is typically based on a column frequently used to access application data. Examples include:

  • Customer ID
  • Account ID
  • Tenant ID
  • Country or region code

For example, consider a global application whose inventory data is partitioned by country. The country identifier can be supplied as the sharding key when the application requests a connection. If that same identifier is also used in SQL predicates, RAC can establish affinity between connections associated with that key and the RAC instance associated with the corresponding data.

Conceptually, the flow is:

Application supplies a sharding key → client driver performs data-dependent connection routing → RAC establishes key-to-instance affinity → application accesses the associated data

The application does not need to implement its own connection-routing logic. The client driver uses the supplied sharding key when obtaining a connection from the connection pool.

Why use RAC sharding

RAC already provides scalability and high availability. RAC sharding optimizes data locality that is particularly useful in applications where transactions repeatedly access data using predictable application or business keys.

  • Improve data locality:  Routing connections based on the sharding key helps establish affinity between application sessions and the data they frequently access. Better locality can reduce the need to access data across RAC instances.
  • Reduce inter-instance traffic: Improved locality can reduce inter-instance communication and associated wait events, allowing more database resources to be used for application work.
  • Adopt incrementally: Applications do not need to make every request shard-aware. Frequently executed, performance-sensitive workloads can supply the sharding key and benefit from affinity, while other requests continue to use standard RAC load balancing.

Implementing RAC sharding

Database configuration

First, identify a frequently accessed table whose access pattern is driven by a suitable sharding key. Application developers will often know which table and key represent the application’s primary access pattern. Database administrators can also use information such as Top SQL and segment activity in AWR reports to help identify candidates. 
For example:

ALTER SYSTEM ENABLE AFFINITY inventories;

The partitioning strategy should provide enough partitions and sufficiently balanced data/workload distribution to make effective use of the available RAC instances. The number of table partitions should be at least equal to the number of RAC instances so that connections can be distributed across all instances. The RAC sharding affinity configuration can be queried using the GV$GWM_RAC_AFFINITY view.

Application configuration

The application supplies the sharding key when requesting a connection. Instead of:

connection = pds.getConnection();

the application can create and provide a sharding key:

OracleShardingKey shardKey =
    pds.createShardingKeyBuilder().
         subkey(myCountry, OracleType.VARCHAR2).
         build();

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

In this example, the inventories table is partitioned using country information, and myCountry is supplied as the sharding key when the connection is requested. The same key is subsequently used in SQL predicates. 

Sizing the Connection pool

Connection pool sizing is important because the pool needs enough connections to establish and maintain awareness of the RAC affinity topology. With sufficient connections distributed across the RAC instances, more requests can be served by connections on the instance associated with their sharding key. For UCP, affinity is a hint by default, providing both data locality and connection flexibility. The application can seamlessly connect to another instance and continue processing transactions when needed, while strict affinity can be enabled for workloads that require connections to remain on the affinitized instance.

Putting RAC sharding to the Test

To quantify the benefit of RAC sharding, we modified the SwingBench Order Entry workload to make it sharding-key aware. After analyzing the SwingBench workload distribution and key cardinality, we selected a three-letter country code as the sharding key, and most transactions included this key in their SQL predicates.  The INVENTORIES table was hash partitioned by COUNTRY_CODE:

CREATE TABLE INVENTORIES (
    PRODUCT_ID       NUMBER(6),
    WAREHOUSE_ID     NUMBER(6),
    COUNTRY_CODE     VARCHAR2(6),
    QUANTITY_ON_HAND NUMBER(8)
)
PARTITION BY HASH (COUNTRY_CODE),PARTITIONS 16;

Application SQL included the same country code in its predicates. For example:

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

This creates a workload in which the application can provide the country code when obtaining a connection and subsequently use that same key when accessing the data. 

Test Environment

The test environment consisted of:

  • 4-node Oracle RAC cluster
  • Exadata X9 system
  • Oracle AI Database 26ai (23.26.1)
  • SwingBench with 512 pooled connections, approximately 128 per RAC instance
  • Scale factor of 100, representing approximately a 100 GB dataset  
Performance Results

In this SwingBench test, RAC sharding delivered two key benefits using the same hardware configuration:

  • Throughput increased by approximately 32%: ~2.2 million → ~2.9 million transactions per minute (TPM)
  • Inter-instance waits decreased from approximately 27% to 1.7% of database time, a 94% relative reduction

The performance chart above illustrates the relationship clearly. As inter-instance waits decreased, workload throughput improved. These results demonstrate the potential benefit of improving data locality for a sharding-key-aware RAC workload. Actual performance gains will depend on factors such as workload characteristics, data-access patterns, partitioning, connection-pool configuration, and system configuration.

When Is RAC sharding a Good Fit?

RAC sharding is worth considering when an application has a combination of these characteristics:

  • OLTP transactions frequently access data using a predictable business or application key, and the application passes that key when connecting to the database.
  • Frequently accessed data can be partitioned using that key.
  • The same key commonly appears in subsequent SQL predicates.
  • Inter-instance data access is a meaningful component of the workload.

Customer ID, account ID, tenant ID, and geographic identifiers are common examples of keys that may fit this access pattern. Not every application module needs to participate. Applications can introduce sharding keys for the workloads where data affinity provides value while other connections continue using standard RAC load balancing.

Bringing Connections Closer to the Data

Oracle RAC already provides scalability and high availability by distributing application workloads across multiple instances. RAC sharding adds data-dependent connection routing so that shard-friendly applications (applications that provide a sharding key during connection) can take advantage of data locality. By establishing affinity between application connections and the corresponding data, RAC sharding can improve cache locality, reduce inter-instance traffic, and provide additional scalability for suitable workloads. As seen in our four-node SwingBench test above, that translated into 32% higher transaction throughput and a 94% relative reduction in inter-instance waits.

Oracle RAC sharding enables applications with key-based data access patterns to use application-provided sharding keys to efficiently scale on Oracle RAC.

Special thanks to Atsushi Morimora for his valuable insignts, feedback, which helped shape this blog post.