How to use services to balance Oracle RAC database workloads

In a previous tip, I explained how DBAs can create workload management services for an organization's Oracle Real...

Application Clusters databases. I recommend setting up at least one service, and maybe more, for each application that will connect to Oracle RAC. In this article, I will show how an organization can use services to help manage the performance of an Oracle RAC database.

Let's start by examining the number of connections, by service, to each instance in a cluster. In the query output below, I removed the default services for brevity. What remains are the services I have defined for two applications that connect to this three-node Oracle RAC system. One service is for the company's ERP application; the other is for its Web servers.

As you can see, I have spread both services across all three database instances. This can sometimes mean that the two applications are competing for the same processing resources. I have a total of 183 connections for the ERP service and 397 for the Web server one. Each instance has fewer than 200 total connections between the two services.

SQL> select service_name,inst_id,count(*)
  2  from gv$session
  3  group by service_name,inst_id
  4  order by service_name,inst_id;

SERVICE_NAME    INST_ID   COUNT(*)
--------------- ---------- ----------
erp_svc                  1         62
erp_svc                  2         60
erp_svc                  3         61
web_svc                  1        130
web_svc                  2        133
web_svc                  3        134

One alternative possibility is to host the ERP service on one node and the Web service on the other two nodes. If I reconfigured my services accordingly, the query results from above might look like this:

SERVICE_NAME    INST_ID   COUNT(*)
--------------- ---------- ----------
erp_svc                  1        183
web_svc                  2        198
web_svc                  3        199

Each instance still has fewer than 200 connections, but now the two applications don't have to compete for the same resources.

With services, the configuration is transparent to the application. A database administrator (DBA) can use Oracle's Server Control Utility, or SRVCTL, to reconfigure the service distribution across the nodes of a cluster. This gives DBAs total control over balancing services to meet the specific needs at their sites. In my example, the commands entered into the utility might be similar to the following:

srvctl modify service -d orcl -s erp_svc -n -i "orcl1" -a "orcl2,orcl3"
srvctl modify service -d orcl -s web_svc -n -i "orcl2,orcl3" -a "orcl1"

The ERP service will now be hosted on the first node. However, should that instance go down, the service could be relocated to the other instances, and the same applies for the Web service if one of its instances becomes unavailable. By configuring the services in this manner, I have segregated application usage, but can still take advantage of the high-availability features of an Oracle RAC database.

Balancing workloads by the number of connections to each service is one way for DBAs to spread out their Oracle RAC services. The Oracle instance has a few dynamic performance views that can assist in balancing services across an Oracle RAC setup. For example, you may want to look at GV$SERVICE_STATS and GV$SERVICEMETRIC. However, one of my favorite views is GV$SERVICE_WAIT_CLASS, which breaks down the "wait events" that slow down processing by their classification, per service and per instance. Here's an example that tracks two types of waits for services associated with Web servers and a customer relationship management (CRM) system:

SQL> SELECT wait_class,service_name,inst_id,total_waits,time_waited
  2  FROM  gv$service_wait_class
  3  WHERE wait_class IN ('Cluster','User I/O')
  4  ORDER BY wait_class,service_name,inst_id;

WAIT_CLASS SERVICE_NAME INST_ID TOTAL_WAITS TIME_WAITED
---------- ------------ ------- ----------- -----------
Cluster    crm_svc            1       12576        7157
Cluster    crm_svc            2       12659        2686
Cluster    web_svc            2        2063         972
User I/O   crm_svc            1       13147        8256
User I/O   crm_svc            2        1433         664
User I/O   web_svc            2       10930        9588

From that query output, we can see that the CRM service is spread over two instances and has a high degree of cluster waits. If that service could be contained on one instance, the cluster waits should lessen due to reduced global cache transfers.

On the other hand, if the DBAs managing the Oracle RAC environment used just one service for all the applications running in the cluster, they wouldn't be able to have any control over the instances used for those connections. With separate services for different applications, a DBA can balance the cluster workload more effectively to achieve better overall application performance in an Oracle RAC database.