10.5 Oracle RAC Data Affinity

Data affinity describes the concept of ensuring that a group of related cache entries is contained within a single cache partition.

Starting from Oracle Database Release 18c, UCP supports Oracle RAC Data Affinity. When you enable Data Affinity on the Oracle RAC database, data on the affinitized tables are partitioned in such a way that a particular partition or subset of rows for a table is affinitized to a particular Oracle RAC database instance. The affinity leads to higher performance and scalability for the applications due to improved cache locality and reduced internode synchronization and block pings among the RAC instances.

To use the Oracle RAC Data Affinity feature, the clients accessing the database through UCP must provide the data affinity key in their connection requests. UCP has the following capabilities when pooling connections for an affinity enabled RAC database:

  1. UCP learns the topology that contains the data affinity of the data partitions across Oracle RAC instances at pool start up. To discover the complete topology, UCP needs to establish at least one connection to each RAC instance. Therefore, configuring a sufficiently large initial pool size is important to ensure that the complete RAC topology is discovered during pool initialization.

  2. UCP connection requests that need to leverage the Oracle RAC Data Affinity feature provides the data affinity key using the sharding key builder and use the connection builder as follows:

      PoolDataSource pds = new PoolDataSourceImpl();
   // configure the data source with the database connection properties

/* Builds the RAC data affinity key using the sharding key builder API
and gets a connection from the pool using UCP connection builder */
  OracleShardingKey dataAffinityKey =  pds.createShardingKeyBuilder()
          .subkey(1000, OracleType.NUMBER)
          .build();
      
  Connection connection = pds.createConnectionBuilder()
          .shardingKey(dataAffinityKey)
          .build();

    Note:

    You can still make connection requests to Oracle RAC Data Affinity-enabled without providing the data affinity key. However, in this case, you will not see the benefits of Oracle RAC Data Affinity feature.
  3. UCP determines the affinitized instance for the shard key provided in the request and checks if a connection for that instance exists in the pool. If the connection exists, then it is used to serve the request. If a matching connection does not exist in the pool, then a fallback to Run-Time Load Balancing chooses a connection for the request and serves it. If a load-balanced connection is also unavailable, then UCP selects an available connection from a non-affinitized RAC instance. If a new connection needs to be created to serve the request, then the request is routed to the affinitized instance corresponding to the provided shard (data affinity) key.
  4. UCP keeps its topology of the data partitions in sync with the server side when there are HA events or when there is a change in the affinity of data partitions on Oracle RAC. To re-synchronize the topology with the server, UCP clears the existing topology information upon receiving HA or routing events. UCP then rebuilds the topology as new connections are established to the RAC instances.

10.5.1 Strict Affinity Mode

By default, affinity is only a hint. If UCP cannot find an available connection to an affinitized RAC instance, it may select an available connection to a non-affinitized instance.

To prevent UCP from selecting available connections from the non-affinitized instances, enable strict affinity mode by setting the following system property:

-Doracle.ucp.jdbc.oracle.affinity.strict=true

When strict affinity mode is enabled, UCP restricts connection selection to the affinitized RAC instance. You can implement this mode in the following two ways:

Strict Affinity with createConnectionInBorrowThread=false (Default)

Starting with Oracle Database 26ai, UCP creates new connections in a background UCP worker thread by default, instead of creating them in the application borrower thread. When strict affinity is enabled in this mode, UCP selects available connections only from the affinitized RAC instance.

During pool warm-up, UCP may not be able to complete the discovery of the entire RAC topology. Similarly, after an HA or routing event, UCP clears its existing topology information and rebuilds it as new connections are established. During these periods, UCP may be unable to determine the affinitized RAC instance.

If UCP cannot determine the affinitized instance or cannot obtain an appropriate connection within the configured connection wait timeout, then the connection borrow request may fail with a UCP-29 error, which is a temporary state. Once UCP discovers the complete RAC topology, it can determine the affinitized instance and route subsequent connection requests accordingly.

Strict Affinity with createConnectionInBorrowThread=true

When you set the createConnectionInBorrowThread system property to true, UCP creates new physical connections in the application borrower thread. A newly created connection can therefore be returned directly to the application.

During pool warm-up or after an HA or routing event, UCP may not be able to complete the discovery of the entire RAC topology. If UCP receives a connection request for a sharding key with unknown affinitized instance, then UCP attempts to create a new connection. As the target instance cannot be determined from the incomplete topology, the new connection may be established to any available RAC instance. As a result, the newly created connection may be associated with a non-affinitized instance, even when the strict affinity mode is enabled. This condition is temporary and can occur only while the RAC topology is incomplete. Once UCP discovers all RAC instances and rebuilds the complete topology, UCP selects or creates connections to the affinitized RAC instance.

Related Topics