Oracle RAC Load balancing Option and Laboratory test & Result

Oracle RAC Loadbalancing Option and Laboratory test and Result
what are new features in Oracle AI 

About Connection Load-Balancing

There are two types of load balancing that you can implement: client-side and server-side load balancing.

With client-side load balancing, connection requests are distributed across the listeners, independently at each client. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service, based on the -clbgoal and -rlbgoal settings for the service.

The SCAN listener is aware of the HTTP protocol. With this awareness, the SCAN can redirect HTTP clients to the appropriate handler, which can reside on different nodes in the cluster, not only the node on which the SCAN listener resides.

In an Oracle RAC database, client connections should use both types of connection load balancing.

 

Server-Side Load Balancing

Using Oracle DBCA to create an Oracle Real Application Clusters (Oracle RAC) database enables you to obtain server-side load-balancing configuration automatically.

When you create an Oracle RAC database with Oracle DBCA, it automatically:

• Configures and enables server-side load balancing
• Creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server

The Oracle Clusterware Database Agent is responsible for managing the LISTENER_NETWORKS parameter.

Note: If you set the REMOTE_LISTENER parameter manually, then set this parameter to scan_name:scan_port.

FAN, Fast Connection Failover, and the load balancing advisory depend on an accurate connection load-balancing configuration that includes setting the connection load-balancing goal for the service. You can use a goal of either LONG or SHORT for connection load-balancing. These goals have the following characteristics:

• SHORT: Use the SHORT connection load-balancing method for applications that benefit from using run-time load-balancing. The following is an example of modifying a service using SRVCTL to set the connection load balancing goal to SHORT:

 

$ srvctl modify service -db db_unique_name -service service_name -clbgoal SHORT

• LONG: Use the LONG connection load-balancing method for applications that benefit from server-side load-balancing and do not require run-time load balancing. LONG is the default connection load balancing goal. The following is an example of modifying a service using SRVCTL to set the connection load balancing goal to LONG:

 

$ srvctl modify service -db db_unique_name -service service_name -clbgoal LONG

 

Generic Database Clients

Oracle Net Services enables you to add the CONNECT_TIMEOUT, RETRY_COUNT, and TRANSPORT_CONNECT_TIMEOUT parameters to the tnsnames.ora connection string.

For example, when using SCAN addresses for the remote listeners at the database:

 

jdbc:oracle:thin:@(DESCRIPTION =

 (TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)

 (RETRY_COUNT=3)(FAILOVER=ON)

 (ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)

  (HOST=CLOUD-SCANVIP.example.com)(PORT=5221))

 (CONNECT_DATA=(SERVICE_NAME=orcl)))

Remote_listeners=CLOUD-SCANVIP.example.com:5221

 

For example, when using remote listeners pointing to VIPs at the database:

 jdbc:oracle:thin:@(DESCRIPTION =

 (TRANSPORT_CONNECT_TIMEOUT=3)

 (CONNECT_TIMEOUT=60)(RETRY_COUNT=20)

 (RETRY_DELAY=3)(FAILOVER=ON)

 (ADDRESS_LIST=

 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP1)(PORT=1521) )

 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP2)(PORT=1521) )

 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP3)(PORT=1521) ))

 (CONNECT_DATA=(SERVICE_NAME=GOLD)))

 

The value of these parameters is expressed in seconds. In the preceding examples, Oracle Net waits for 60 seconds for each full connection to receive a response, after which it assumes that a failure occurred and retries the next address in the ADDRESS_LIST. Oracle Net retries the address list 3 times before it returns a failure message to the client. The TRANSPORT_CONNECT_TIMEOUT parameter establishes the time to wait to establish a TCP connection to the database server.

For SCAN, Oracle Net Services tries all three addresses (returned by the SCAN address) before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

This behavior is called Oracle Net connection failover. If an error is returned from a chosen address in the list, then Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list.

 

About Client-Side Connection Configuration for Older Clients

Oracle Net Services provide connection failover and availability features for service requests from older clients.

In addition to client-side load balancing, Oracle Net Services include connection failover. If an error is returned from the chosen address in the list, Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list. For SCAN, Oracle Net Services tries all three addresses before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

To increase availability, you can specify a timeout that specifies how long Oracle Net waits for a response from the listener before returning an error. The method of setting this timeout parameter depends on the type of client access. Oracle Net maintains these parameters for backward compatibility.

 

Client-Side Load Balancing

Learn about client-side load balancing, and how a Single Client Access Name (SCAN) can assist with connection loads.

Client-side load balancing is defined in your client connection definition (tnsnames.ora file, for example) by setting the parameter LOAD_BALANCE=ON. When you set this parameter to ON, Oracle AI Database randomly selects an address in the address list, and connects to that node's listener. This balances client connections across the available SCAN listeners in the cluster.

If you configured SCAN for connection requests, then client-side load balancing is not relevant for those clients that support SCAN access. When clients connect using SCAN, Oracle Net automatically balances the load of client connection requests across all available IP addresses you defined for the SCAN, unless you are using Easy Connect string.

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded (if -clbgoal is set to SHORT) and provides the requested service. When the listener receives the connection request, the listener connects the user to an instance that the listener knows provides the requested service. To see what services a listener supports, run the lsnrctl services command.

Note: Oracle recommends -clbgoal LONG for client-side load balancing.

If you are using clients that do not support SCAN, then, to use SCAN you must change the client tnsnames.ora to include the SCAN VIPs, and set LOAD_BALANCE=ON to balance requests across the VIPs. For example:

 Sales.example.com=(DESCRIPTION=

  (ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)

    (ADDRESS=(PROTOCOL=TCP)(HOST=172.1.67.190)(PORT=1521))

    (ADDRESS=(PROTOCOL=TCP)(HOST=172.1.67.191)(PORT=1521))

    (ADDRESS=(PROTOCOL=TCP)(HOST=172.1.67.192)(PORT=1521)))

    (CONNECT_DATA=(SERVICE_NAME=mysrv.example.com))  )

 

Overview of the Load Balancing Advisory

Learn about load balancing, and about guidelines that Oracle recommends for load balancing on Oracle Real Application Clusters (Oracle RAC).

Load balancing distributes work across all of the available Oracle RAC database instances. Oracle recommends that applications use connection pools with persistent connections that span the instances that offer a particular service. When using persistent connections, connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exists for a relatively short duration. The load balancing advisory provides advice about how to direct incoming work to the instances that provide the optimal service quality for that work. This minimizes the need to relocate the work later.

By using the Load Balancing Advisory and run-time connection load balancing goals, feedback is built in to the system. Work is routed to provide the best service times globally, and routing responds gracefully to changing system conditions. In a steady state, the system approaches equilibrium with improved throughput across all of the Oracle RAC instances.

Standard architectures that can use the load balancing advisory include connection load balancing, transaction processing monitors, application servers, connection concentrators, hardware and software load balancers, job schedulers, batch schedulers, and message queuing systems. All of these applications can allocate work.

The load balancing advisory is deployed with key Oracle clients, such as a listener, the JDBC universal connection pool, OCI session pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and the ODP.NET Connection Pools.

Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with OCI.

 

Configuring Your Environment to Use the Load Balancing Advisory

You can configure your environment to use the load balancing advisory by defining service-level goals for each service for which you want to enable load balancing.

Configuring a service-level goal enables the load balancing advisory and the publishing of FAN load balancing events for that service. There are three types of service-level goals for run-time connection load balancing:

• SMART_CONN: Enables Smart Connection Rebalance and tries to ensure that workloads accessing similar objects end up in one instance and benefit from the reduced inter-instance network messages and data block transfers over the private network. The following example shows how to set the goal to SMART_CONN for connections using the odpapp service:

 

$ srvctl modify service -db db_unique_name -service odpapp  -rlbgoal SMART_CONN

• SERVICE_TIME: Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE_TIME is for workloads such as internet shopping where the rate of demand changes. The following example shows how to set the goal to SERVICE_TIME for connections using the online service:

 

$ srvctl modify service -db db_unique_name -service online  -rlbgoal SERVICE_TIME -clbgoal SHORT

• THROUGHPUT: Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. An example for the use of THROUGHPUT is for workloads such as batch processes, where the next job starts when the last job completes. The following example shows how to set the goal to THROUGHPUT for connections using the sjob service:

 

$ srvctl modify service -db db_unique_name -service sjob  -rlbgoal THROUGHPUT -clbgoal LONG

Setting the run-time connection load balancing goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary by querying the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views. You can also review the load balancing settings for a service using Oracle Enterprise Manager.

 

Load Balancing Advisory FAN Events

The load balancing advisory FAN events provide metrics for load balancing algorithms.

The easiest way to take advantage of these events is to use the run-time connection load balancing feature of an Oracle integrated client such as JDBC, Universal Connection Pool (or the deprecated Implicit Connection Cache), ODP.NET Connection Pools, OCI session pools, or Oracle WebLogic Server Active Grid Link for Oracle RAC. Other client applications can take advantage of FAN programatically by using the Oracle RAC FAN API to subscribe to FAN events and perform event-handling actions upon receipt. Following table describes the load balancing advisory FAN event parameters.

Table 1: Load Balancing Advisory FAN Events

Parameter	Description
VERSION	Version of the event record. Used to identify release changes.
EVENT_TYPE	A load balancing advisory event is always of the SERVICEMETRICS event type.
SERVICE	The service name; matches the value of NAME in DBA_SERVICES.
DATABASE	The unique database supporting the service; matches the initialization parameter value for DB_UNIQUE_NAME, which defaults to the value of the initialization parameter DB_NAME.
INSTANCE	The name of the instance that supports the service; matches the ORACLE_SID value.
PERCENT	The percentage of work requests to send to this database instance.
FLAG	Indication of the service quality relative to the service goal. Valid values are GOOD, VIOLATING, NO DATA, and BLOCKED.
TIMESTAMP	The local time zone to use when ordering notification events.

 

Note: The INSTANCE, PERCENT, and FLAG event parameters are generated for each instance offering the service. Each set of instance data is enclosed within braces ({}).

 

Monitoring Load Balancing Advisory FAN Events

To monitor load balancing advisory events for an instance, use this query.

You can use the following query against the internal queue table for load balancing advisory FAN events to monitor load balancing advisory events generated for an instance:

 

SET PAGES 60 COLSEP '|' LINES 132 NUM 8 VERIFY OFF FEEDBACK OFF

COLUMN user_data HEADING "AQ Service Metrics" FORMAT A60 WRAP

BREAK ON service_name SKIP 1

SELECT

 TO_CHAR(enq_time, 'HH:MI:SS') Enq_time, user_data

 FROM sys.sys$service_metrics_tab

 ORDER BY 1 ;

 

The results of this query contain rows similar to the following:

 

02:56:05|SYS$RLBTYP('hr', 'VERSION=1.0 database=sales service=hr

   { {instance=sales_4 percent=38 flag=GOOD aff=TRUE}{instance=sales_1

   percent=62 flag=GOOD aff=TRUE} } timestamp=2012-07-16 07:56:05')

Following is an example of a load balancing advisory event for the lba_serv service offered on two instances (orcl1 and orcl2), as captured from Oracle Notification Service using the Oracle RAC FAN API:

 

Notification Type: database/event/servicemetrics/lba_serv.example.com

   VERSION=1.0 database=orcl service=lba_serv.example.com { {instance=orcl2

   percent=50 flag=UNKNOWN aff=FALSE}{instance=orcl1 percent=50 flag=UNKNOWN

   aff=FALSE} } timestamp=2012-07-06 13:19:12

Note

The SERVICMETRICS events are not visible through the FAN callout mechanism.

 

***Smart Connection Rebalance***

Smart Connection Rebalance automatically routes sessions to an instance with the intent to optimize performance by monitoring the access patterns of the underlying objects of the workload.

Oracle Real Application Clusters (Oracle RAC) offers two options for load balancing: client-side load balancing and server- side load balancing. Sessions connect to an Oracle RAC instance using Single Client Access Network (SCAN) and a user-defined service name. You can configure a service to run on all or a subset of Oracle RAC instances. By default, SCAN redirects the sessions to the local listener and the SCAN listener directs a connection request to the best instance currently hosting the service, based on the -clbgoal and -rlbgoal settings for the service.

Smart Connection Rebalance avoids resource conflict and ensures that workloads accessing similar objects end up in one instance and benefit from the reduced inter-instance network messages and data block transfers over the private network. This feature ensures optimum load balancing and performance. Oracle RAC features, such as partitioning, local indexes, Right Growing Index (RGI) optimizations, and Exafusion help reduce resource conflict.

You can enable Smart Connection Rebalance by setting the -rlbgoal attribute to SMART_CONN:

$ srvctl modify service -db db_unique_name -service service_name -rlbgoal SMART_CONN

To disable Smart connection load balancing, set the -rlbgoal of that service to Service_TIME.

This feature performs real-time monitoring of different workloads and attempt to transparently relocate service-based connections across Oracle RAC instances to significantly improve database performance.

Note:The connection relocation is automatic and does not need database administrators to manually distribute the sessions.

 

My laboratory test and results:

For the past few days, I've been spending some time testing Oracle Database 26ai RAC connection management in my lab, specifically around Connection Load Balancing, Load Balancing Advisory, and the new Smart Connection Rebalance capability.

One thing became very clear...

Many Oracle RAC environments are still using the same service configuration they had years ago, while Oracle has significantly improved how connections should be distributed.

A few observations from my tests:

 

Tip #1: Client-Side Load Balancing is only the first step

Many DBAs simply enable

LOAD_BALANCE=ON

inside tnsnames.ora and assume everything is balanced.

Actually, this only randomizes the initial listener selection (or SCAN address selection for older clients).

It does not decide which RAC instance is the best destination for the workload.

That decision belongs to the server.

 

Tip #2: Server-Side Load Balancing does the real work

Once the connection reaches the SCAN Listener, Oracle evaluates the service attributes.

The most important one is

-clbgoal

which controls Connection Load Balancing Goal.

Example:

srvctl modify service -db PROD -service OLTP  -clbgoal SHORT

or

srvctl modify service -db PROD  -service DW  -clbgoal LONG

This tiny parameter has a surprisingly large impact on connection placement.

 

Tip #3: SHORT is not always better

This was probably the biggest surprise in my lab.

Many people assume SHORT should always be used because it sounds more "dynamic."

Oracle's recommendation is actually more nuanced.

Use LONG when:

• connections are persistent
• connection pools are used
• sessions live for a long time
• client-side balancing is already enabled

Use SHORT when:

• response time changes rapidly
• runtime load balancing is required
• application servers continuously borrow and return pooled sessions

Choosing SHORT for every service can actually create unnecessary connection movement.

 

Tip #4: Load Balancing Advisory is where Oracle becomes "Smart"

One feature that doesn't receive enough attention is the Load Balancing Advisory (LBA).

Instead of blindly distributing work evenly, Oracle continuously measures service quality and publishes metrics through FAN.

Integrated clients like

• JDBC UCP
• OCI Session Pools
• ODP.NET
• WebLogic Active GridLink

can consume these metrics automatically.

Instead of asking:

Which instance has fewer sessions?

Oracle asks:

Which instance can finish this work faster?

Ø  That's a much better metric.

 

Tip #5: Oracle 26ai introduces Smart Connection Rebalance

This was my favorite feature during testing.

Instead of simply balancing session counts, Oracle can now relocate new service connections based on object access patterns.

Configuration is surprisingly simple:

srvctl modify service -db PROD -service SALES -rlbgoal SMART_CONN

Oracle then attempts to keep sessions accessing the same tables, indexes, and partitions on the same RAC instance.

The result?

✔ fewer Global Cache transfers

✔ less interconnect traffic

✔ reduced block shipping

✔ better cache locality

This is a very different philosophy from traditional round-robin balancing.

 

Tip #6: Connection balancing is only as good as the service design

One lesson I keep seeing in customer environments:

People spend weeks tuning SQL...

...while every application still connects to a single generic service.

In RAC, services are the real workload management layer.

A well-designed service strategy usually delivers more benefit than changing dozens of initialization parameters.

 

Oracle's own recommendation

For most modern applications using connection pools:

       Use SCAN

       Enable Client-Side Load Balancing

       Configure appropriate Server-Side Load Balancing

       Define the correct -clbgoal for each service

       Enable Load Balancing Advisory

       Consider -rlbgoal SMART_CONN in Oracle Database 26ai for object-aware workload placement

Sometimes the biggest performance gain isn't faster SQL, it's sending the session to the right instance before the SQL even begins.

 

Tip #7: Load Balancing and High Availability are different problems

One misconception I still encounter is that Load Balancing equals High Availability.

They solve different problems.

• Client-Side Load Balancing spreads new connection requests.
• Server-Side Load Balancing chooses the best RAC instance for the workload.
• Load Balancing Advisory (LBA) continuously provides service-quality metrics to intelligent clients.
• Transparent Application Continuity (TAC) protects the application when that chosen instance becomes unavailable by transparently replaying recoverable requests.

Think of it this way:

Load Balancing decides where you connect.

Transparent Application Continuity decides what happens if that connection disappears.

Both are needed for a resilient Oracle RAC deployment.

 

Tip #8: The real power comes from combining Oracle RAC features

One thing I appreciated during testing is how Oracle designed these features to complement each other rather than operate independently.

A modern RAC service can combine:

• SCAN listeners
• Client-Side Load Balancing
• Server-Side Load Balancing
• Load Balancing Advisory
• FAN (Fast Application Notification)
• Connection pools (UCP, OCI Session Pool, ODP.NET, WebLogic Active GridLink)
• Transparent Application Continuity
• Smart Connection Rebalance (26ai)

Instead of viewing them as separate features, it's better to think of them as a complete connection-management stack.

 

Tip #9: Don't forget the service attributes

During my lab tests, I realized that many administrators configure services with only a preferred instance list and stop there.

The real behavior is driven by the service attributes, for example:

srvctl modify service -db PROD -service SALES -clbgoal LONG -rlbgoal SMART_CONN -failovertype AUTO

Now Oracle can:

• place new connections intelligently,
• publish FAN events,
• rebalance new sessions based on workload affinity (26ai),
• and automatically recover eligible sessions after planned or unplanned outages using Transparent Application Continuity (TAC).

 

Conclusion:

My biggest takeaway from these tests is that Oracle RAC performance isn't just about SQL tuning anymore.

A well-designed service, configured with the right load balancing goals, FAN, Transparent Application Continuity, and, in Oracle Database 26ai, Smart Connection Rebalance, can improve scalability, reduce interconnect traffic, and make maintenance events almost invisible to applications. That's the kind of optimization users actually notice.

This ties together the evolution of Oracle RAC: 19c introduced mature service management, FAN, and Transparent Application Continuity, while 26ai extends that foundation with Smart Connection Rebalance, which adds workload-aware placement based on object affinity rather than just response time or connection counts.

******************************** Alireza Kamrani *******************************