Workload Management with Dynamic Database Services

Workload management includes load balancing, enabling clients for Oracle Real Application Clusters (Oracle RAC), distributed transaction processing, and services.

Connection Load-Balancing

Oracle Net Services supports client-side and server-side connection load balancing for Oracle RAC. Client-side balancing distributes requests across listeners, while server-side balancing uses SCAN listeners and service goals such as -clbgoal and -rlbgoal to select the best instance. Oracle recommends using both methods, with DBCA configuring them automatically. Connection failover and timeout settings such as CONNECT_TIMEOUT, RETRY_COUNT, RETRY_DELAY, and TRANSPORT_CONNECT_TIMEOUT improve availability for generic, JDBC, and OCI clients, while LOAD_BALANCE=ON distributes older-client connections across SCAN addresses.

Smart Connection Rebalance

Smart Connection Rebalance analyzes workload access patterns and automatically routes service connections to instances where related workloads can share data efficiently, reducing inter-instance messaging, global-cache transfers, contention, and response times. Its recommendation engine evaluates services and smart connection groups, reports guidance through AWR and the SMART_CONN_RECOMMENDATION column of (G)V$ACTIVE_SERVICES, and can relocate service connections without manual session redistribution. Enable it with srvctl modify service ... -rlbgoal SMART_CONN; disable it by setting the goal to NONE, with child services restricted to SMART_CONN or NONE.

Load Balancing Advisory

The Load Balancing Advisory directs work toward RAC instances offering the best service quality and supports connection pools, application servers, schedulers, middleware, and integrated Oracle clients. Service goals include SMART_CONN for workload locality, SERVICE_TIME for response-time-sensitive work, and THROUGHPUT for batch-oriented workloads; NONE disables runtime balancing. FAN service-metrics events provide instance-level routing percentages and service-quality flags, and clients can consume them through JDBC, UCP, OCI session pools, ODP.NET, WebLogic Active GridLink, or the Oracle RAC FAN API. Events can be monitored through internal service-metrics queues, although they are not exposed through FAN callouts.

Enabling Clients for Oracle RAC

Oracle integrates FAN with JDBC, OCI, UCP, WebLogic Active GridLink, ODP.NET, and related connection pools to detect failures quickly, remove invalid connections, rebalance pools, and exploit newly available instances. JDBC FCF requires enabling setFastConnectionFailoverEnabled(true), configuring Oracle Notification Service, and using service-name connection URLs; runtime load balancing additionally requires service-level advisory goals. OCI clients register callbacks for FAN events and can use TAF, service notification, and advisory settings, while ODP.NET enables HA and load balancing through connection-string attributes such as HA Events=true and Load Balancing=true. Application Continuity and Transaction Guard support replay and reliable transaction-outcome handling after outages.

Distributed Transaction Processing in Oracle RAC

Oracle RAC supports global XA transactions across instances through GTX background processes, with XA affinity recommended to keep all branches of a transaction on one instance for performance and correct handling of suspend/resume or cross-branch savepoints. Application servers and connection pools such as WebLogic and UCP can provide XA affinity, while singleton services can provide preferred-instance placement and distribute workloads across multiple service groups. DTP services using -dtp TRUE should be reserved for applications that require them; Oracle generally recommends global transactions with XA affinity instead. Service relocation and failover remain available for singleton-service deployments.

Automatic Workload Repository

The Automatic Workload Repository collects, processes, and preserves database and service-level performance statistics for reporting, alerting, workload management, and resource prioritization. Metrics can represent time, transactions, calls, and other measures, enabling administrators to adjust Resource Manager priorities, stop overloaded processes, modify service requirements, or execute recovery actions. AWR provides persistent, global performance data for Oracle Clusterware and Resource Manager and supports manual workload rebalancing through Enterprise Manager or SRVCTL. RAC services should be created and managed with SRVCTL or Enterprise Manager rather than DBMS_SERVICE.

Measuring Performance by Service Using the Automatic Workload Repository

AWR enables performance tuning by service, making response time, throughput, resource use, waits, and SQL activity attributable to applications rather than anonymous sessions. Administrators can extend monitoring to specific modules and actions with DBMS_MONITOR, verify enabled aggregations through DBA_ENABLED_AGGREGATIONS, and inspect service data in views such as V$SERVICE_STATS, V$SERVICE_EVENT, V$SERVICE_WAIT_CLASS, V$SERVICEMETRIC, V$SERVICEMETRIC_HISTORY, and V$SERV_MOD_ACT_STATS. These global, persistent statistics support service-quality monitoring, workload routing, and balancing across RAC instances.

Automatic Workload Repository Service Thresholds and Alerts

AWR service thresholds compare actual service quality with required response-time and CPU-time levels using moving averages of server-side call metrics. Administrators can define warning and critical limits with DBMS_SERVER_ALERT, apply them across RAC instances, review configurations in DBA_THRESHOLDS, and trigger Enterprise Manager or programmatic actions such as changing job priorities, stopping overloaded processes, or relocating services. DBMS_MONITOR can also enable service, module, and action monitoring, with results verified through DBA_ENABLED_AGGREGATIONS and detailed in V$SERV_MOD_ACT_STATS.

Using Oracle Services

Oracle services provide logical workload groupings that can represent applications, user populations, or operation types such as online processing, batch jobs, and reporting. Assigning connections and work to distinct services makes resource consumption, performance, and service-level requirements visible and manageable. Services can be configured with characteristics that support workload isolation, monitoring, prioritization, availability, and application performance optimization.

Service Deployment Options

Oracle RAC services provide location transparency by allowing a service name to identify multiple instances while allowing an instance to support multiple services. Service definitions control Clusterware resources and failover, Resource Manager consumer-group mappings, AWR monitoring, parallel-query instance scope, GoldenGate queue ownership, editions, startup policy, Data Guard roles, preferred and available instances, failback, co-location, connection and runtime load balancing, and XA behavior. Services should use unique valid names and appropriate -clbgoal and -rlbgoal settings; default internal services are not client-managed. Restricted service registration protects listeners through valid-node checking and configurable invited nodes or subnets.

Administering Services

Oracle Enterprise Manager and SRVCTL provide the primary methods for creating and administering Oracle RAC services; SRVCTL settings override DBMS_SERVICE settings. Service administration includes creating, deleting, starting, stopping, enabling, disabling, relocating, monitoring, and mapping services to Resource Manager consumer groups. Services should be configured with workload-management characteristics, and SERVICE_NAMES should not be set manually. Enterprise Manager provides cluster-wide service management but requires SYSDBA credentials, while SRVCTL supports detailed command-line administration, including Application Continuity and Transaction Guard configuration through failover, load-balancing, notification, retention, and replay parameters.

Global Services

Global services provide centralized management and workload management across replicated Oracle RAC and nonclustered databases connected through technologies such as Oracle Data Guard or GoldenGate. They support preferred and available databases, replication-lag handling, geographical affinity, connection and runtime load balancing, inter-database failover, fast and connect-time failover, Application Continuity, Transaction Guard, and compatibility with existing clients. SRVCTL manages instance placement within an Oracle RAC database, while GDSCTL manages other global service attributes.

Service-Oriented Buffer Cache Access

Service-oriented buffer cache access improves performance by tracking object access through services and persisting that information in the database. Frequently accessed blocks are cached on instances running the associated service rather than on unrelated instances, and the cache can be pre-warmed when a service starts or relocates. This preserves more consistent performance for users after service placement changes.

Connecting to a Service: An Example

This example demonstrates configuring and using an HR.example.com service on the CRM database, with preferred instances CRM1 and CRM2, available instances CRM3 and CRM4, cardinality of two, LONG connection load balancing, SERVICE_TIME runtime load balancing, and Application Continuity settings. SRVCTL creates and starts the service with transaction failover, commit outcome tracking, a 10-second retry delay, and up to 40 retries. A JDBC Universal Connection Pool application then connects through the SCAN listener using the service name, Fast Connection Failover, remote ONS, and configured pool credentials and sizing.