7 Managing Database Workload Using Services

Using workload management, you can distribute the workload across database instances to achieve optimal database and cluster performance for users and applications. This chapter contains the following sections:

About Workload Management

To implement workload management for an Oracle Real Application Clusters (Oracle RAC) database, you can several different features. This section contains the following topics:

You can deploy Oracle RAC and single-instance Oracle database environments to use workload management features in many different ways. Depending on the number of nodes and your environment's complexity and objectives, your choices for the optimal workload management and high availability configuration depend on several considerations that this chapter describes.

About Oracle Services

Oracle Database 10g introduces an automatic workload management facility, called services. A service represents the workload of applications with common attributes, performance thresholds, and priorities. A single service can represent an application, multiple applications or a subset of a single application. A single service can be associated with one or more instances of an Oracle RAC database, and a single instance can support multiple services. Services provide a single system image to manage competing applications, and they allow each workload to be managed as a single unit.

To manage workloads, you can define services that you assign to a particular application or to a subset of an application's operations. You can also use services to manage the workload for different types of work. For example, online users can use one service while batch processing can use a different service and reporting can use yet another service type.

When a user or application connects to a database, Oracle recommends that you use a service for the connection. Oracle Database automatically creates one database service when the database is created. For many installations, this may be all you need. For more flexibility in the management of the workload using the database, Oracle Database enables you to create multiple services and specify which database instances offer the services.

Services are integrated with the Resource Manager, which enables you to restrict the resources that are used by a service within an instance. In addition, Oracle Scheduler jobs can run under a service, as opposed to a specific instance.

Configuring Services for High Availability

When you create a service, you define which instances typically support that service. These are known as the preferred instances for that service. You can also define other instances to support a service if the service's preferred instance fails. These are known as available instances for a service.

When you specify a preferred instance for a service, the service runs on that instance during standard operation. Oracle Clusterware attempts to ensure that the service always runs on all the preferred instances that have been configured for a service. If the instance fails, the service is randomly relocated to one of the available instances. You can also manually relocate the service to an available instance. If you do not specify preferred or available instances when you create a service, then by default every instance in the Oracle RAC database is a preferred instance for that service.

If a service fails over to an available instance, the service is not moved back to its preferred instance automatically. However, you can automate the relocation of a service to its preferred instance by using a callout. For more information about callouts, see "About FAN Callouts". An example callout script for relocating services back to their preferred instances is available in the Oracle Real Application Clusters Sample Code section on Oracle Technology Network at http://www.oracle.com/technology/sample_code/products/rac/index.html

You do not have to specify available instances for a service. However, if you configure a preferred instance for a service, but do not specify at least one available instance for the service, then the service does not relocate to another instance if the preferred instance fails.

You can also specify an instance as Not Used. This setting means the service does not run on the instance, even if the preferred instance for the service fails.

About the Database Resource Manager

The Database Resource Manager controls database resources allocated to users, applications, and services. This approach ensures that users, applications, and services receive their share of the available database resources. The Database Resource Manager enables you to map a consumer group to a service so that users who connect with the service are members of the specified consumer group. The Database Resource Manager allows a single Oracle database running on one or more computers to support multiple applications and mixed workloads with optimal efficiency.

The Database Resource Manager provides the ability to prioritize work within the Oracle database or your Oracle RAC environment. For example, high priority users, such as online workers, would get more resources to minimize response time, while lower priority users, such as batch jobs or reports, could take longer to run. This allows for more granular control over resources.

Resources are allocated to users according to a resource plan specified by the database administrator. The following terms are used in specifying a resource plan:

  • A resource plan specifies how the resources are to be distributed among various users (resource consumer groups).

  • Resource consumer groups allow the administrator to group user sessions together by resource requirements. Resource consumer groups are different from user roles; one database user can have different sessions assigned to different resource consumer groups.

  • Resource allocation methods are the methods or policies used by the Database Resource Manager when allocating for a particular resource. Resource allocation methods are used by resource consumer groups and resource plans. The database provides the resource allocation methods that are available, but you determine which method to use.

  • Resource plan directives are a means of assigning consumer groups to particular plans and partitioning resources among consumer groups by specifying parameters for each resource allocation method.

  • Subplans, which you can create within a resource plan, allow further subdivision of resources among different users of an application.

  • Levels provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified.

See Also:

Oracle Database Administrator's Guide for more information about the Database Resource Manager

About Oracle RAC High Availability Framework

The Oracle RAC high availability framework enables Oracle Database to maintain components in a running state at all times. If a component fails, it can be automatically restarted to keep Oracle Database operating at full capacity

Oracle Database focuses on maintaining service availability. In Oracle RAC, Oracle services are designed to be continuously available with workloads shared across one or more instances. The Oracle RAC high availability framework maintains service availability by storing the configuration information for each service in the Oracle Cluster Registry (OCR). Oracle Clusterware recovers and balances services across instances based on the service definition.

Enabling the Load Balancing Advisory

The Load Balancing Advisory provides information to applications or clients about the current service levels that the Oracle RAC database instances are providing. The Load Balancing Advisory makes recommendations to applications about which instance to direct application requests for a database service to obtain the best performance based on the policy that you have defined for that service.

When the Load Balancing Advisory is enabled, it will account for the following when balancing connection requests across instances:

  • Server power differences

  • Sessions that are blocked or waiting

  • Failures that slow down processing

Using the Load Balancing Advisory prevents sending work to Oracle RAC instances that are overworked, not responding, or not available.

The Load Balancing Advisory informs the application or client about the current performance level that an Oracle RAC database instance is providing for a service. The Load Balancing Advisory also recommends how much of the workload should be sent to that instance.

The best way to take advantage of the Load Balancing Advisory is to use an integrated Oracle client, one that has the Runtime Connection Load Balancing feature. Integrated Oracle clients subscribe to the Load Balancing Advisory FAN events.

You configure your Oracle RAC environment to use the Load Balancing Advisory by defining service-level goals for each service used. This enables the Load Balancing Advisory for that service and enables the publication of FAN load balancing events. There are two types of service-level goals for Runtime Connection Load Balancing:

  • Service Time—The Load Balancing Advisory attempts to direct work requests to instances according to their response time. Load Balancing Advisory data is based on the elapsed time for work done in the service as well as available bandwidth to the service.

  • Throughput—The Load Balancing Advisory measures the percentage of the total response time that the CPU consumes for the service. This goal measures the efficiency of an instance, rather than the response time.

If you do not select the Enable Load Balancing Advisory option, the service-level goal is set to None, which disables load balancing for the service.

About Connection Load Balancing

Oracle Net is a software component that resides on the client and on the Oracle database server. It establishes and maintains the connection between the client application and the server, and exchanges messages between them using industry standard protocols. For the client application and a database to communicate, the client application must specify location details for the database it wants to connect to, and the database must provide some sort of identification or address.On the database server, the Oracle Net Listener, commonly known as the Listener, is a process that listens for client connection requests. The configuration file for the Listener is the listener.ora.The client uses a connect descriptor to specify the database to which to connect. This connect descriptor contains a protocol and a database service name. When a client requests a connection, the Listener on the server receives the request and forwards the connection to the Oracle database. You can define your connect descriptors in the tnsnames.ora file on the client machine, or include them as part of the connection request.When the client connects to the cluster database using a service, you can use the Oracle Net connection load balancing feature to spread user connections across all of the instances that are supporting that service. There are two types of load balancing that you can implement: client-side and server-side load balancing.In an Oracle RAC database, client connections should use both types of connection load balancing. When you create an Oracle RAC database using Oracle Database Configuration Assistant (DBCA), DBCA configures and enables server-side load balancing by default.

See Also:

Oracle Database 2 Day DBA for more information about Oracle Net configuration

Client-Side Load Balancing

Client-side load balancing balances the connection requests across the Listeners. When the Listener receives the connection request, the Listener connects the user to an instance that the Listener knows provides the requested service.

Client-side load balancing is defined in your client connection definition by setting the parameter LOAD_BALANCE=yes in the tnsnames.ora file. When you set this parameter to yes, the Oracle client randomly selects an address from the address list, and connects to that node's Listener. This balances client connections across the available Listeners in the cluster.

When you create an Oracle RAC database with DBCA, it creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server.

Client-side load balancing includes connection failover. With connection failover, if an error is returned from the chosen address, Oracle Net Services will try the next address in the address list until either a successful connection is made or it has exhausted all the addresses in the list.

Server-Side Load Balancing

With server-side load balancing, the Listener directs a connection request to the best instance currently providing the service by using information from the Load Balancing Advisory.

For each service, you can define the method that you want the Listener to use for load balancing by setting the connection load balancing goal. You can use a goal of either long or short for connection load balancing. These goals have the following characteristics:

  • Short—Connections are distributed across instances based on the elapsed time under the service. Use the Short connection load balancing goal for applications that have connections of small duration.

  • Long—Connections are distributed across instances based on the number of sessions per instance, for each instance that supports the service. Use the Long connection load balancing goal for applications that have connections of long duration. This is typical for connection pools and SQL*Forms sessions. Long is the default connection load balancing goal.

Any services created by using DBCA use the Long connection load balancing goal by default.


If you did not use DBCA to create your database, or if you are using Listener ports other than the default of 1521, then you must configure the LOCAL_LISTENER and REMOTE_LISTENER database initialization parameters for your cluster database.

About Runtime Connection Load Balancing

Runtime Connection Load Balancing is a feature of Oracle connection pools that can distribute client work requests across the instances in an Oracle RAC database based on the Load Balancing Advisory information. The connection allocation is based on the current service level provided by the database instances as indicated by the Load Balacing Advisory FAN events. This provides load balancing at the transaction level instead of load balancing at the time of the initial database connection.

With Runtime Connection Load Balancing, applications use Load Balancing Advisory information to provide better performance to users. The Oracle JDBC and Oracle Data Provider for .NET (ODP.NET) client connection pools are integrated to take advantage of Load Balancing Advisory information. You must enable the client data source for Runtime Connection Load Balancing with a service that has the following configuration:

  • The Load Balancing Advisory is enabled and the service-level goal is set to either Service Time or Throughput.

  • The service connection load balancing goal is set to Short.

Figure 7-1, "Runtime Connection Load Balancing" illustrates Runtime Connection Load Balancing. In this example, the Oracle RAC database has three instances. Suppose that the Load Balancing Advisory indicates that Instance1 and Instance3 have the best performance, while Instance2 currently has less than optimal performance. When Runtime Connection Load Balancing is enabled on the implicit connection cache, the following process occurs:

  1. A client requests a connection from the connection cache.

  2. Runtime Connection Load Balancing selects the connection that belongs to the best instance from the connection cache. In Figure 7-1, there are three possible nodes the connection can be routed to. Instance1, which has the least amount of CPU workload is currently being assigned about 60% of the incoming connections. Instance2, which is currently overloaded, is only being assigned around 10% of the incoming connections. Instance3, which has a high workload, is being assigned around 30% of the incoming connections. The best instance to handle the connection request in this case would be Instance1.

  3. The client receives the connection that would process the work request with the best response time.

Figure 7-1 Runtime Connection Load Balancing

Description of Figure 7-1 follows
Description of "Figure 7-1 Runtime Connection Load Balancing"

About Fast Application Notification (FAN)

Fast Application Notification is a notification mechanism that Oracle RAC uses to notify other processes about cluster configuration and service-level information, including status changes such as UP or DOWN events. FAN UP and DOWN events can apply to instances, services, and nodes. FAN also publishes Load Balancing Advisory events.

For cluster configuration changes, the Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster. Instead of waiting for the application to query the database and detect a problem, applications can receive FAN events and react immediately.

FAN UP and DOWN events provide the following benefits:

  • For DOWN events, the disruption to the application can be minimized because sessions to the failed instance or node can be terminated. Incomplete transactions can be terminated and the application user is immediately notified. Application users who request connections are directed to instances that are started and are providing the requested service.

  • For UP events, when services and instances are started, new connections can be created so that the application can immediately take advantage of the extra resources.

About FAN Callouts

FAN callouts are server-side executable files that Oracle RAC executes immediately when high availability events occur. Some examples of how you can use FAN callouts to automate the actions performed when events occur in a cluster configuration are as follows:

  • Starting and stopping server-side applications

  • Relocating low-priority services when high-priority services come online

  • Sending text or numeric messages to pagers

  • Executing shell scripts

The executable files for FAN callouts are stored in the racg/usrco subdirectory of the Oracle Clusterware home directory. If this subdirectory does not exist in your Oracle Clusterware home, then you must create this directory with the same permissions and ownership as the Oracle Clusterware home racg/tmp subdirectory.

A copy of the executable files used by FAN callouts should be available on every node that runs Oracle Clusterware. Example callout scripts are available in the Oracle Real Application Clusters Sample Code section on Oracle Technology Network at http://www.oracle.com/technology/sample_code/products/rac/index.html

See Also:

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about configuring Fast Application Notification and FAN callouts

Creating Services

You can create a service using Oracle Enterprise Manager Database Control.

To create a service:

  1. On the Cluster Database Home page, click Maintenance.

    Description of wlm001.gif follows
    Description of the illustration wlm001.gif

  2. Click Cluster Managed Database Services in the High Availability section. Enter or confirm the preferred credentials for the Oracle RAC database and host.

    The Cluster Managed Database Services page appears.

    Description of wlm005.gif follows
    Description of the illustration wlm005.gif

  3. Click Create Service.

    The Create Service page appears.

  4. Enter the name of your service in the Service Name field, for example, DEVUSERS.

  5. If you want the service to be started after it is created, then select Start Service after creation.

    Description of wlm006.gif follows
    Description of the illustration wlm006.gif

  6. For each instance displayed in the High Availability section, you can choose whether the instance is a Preferred or Available instance for this service. If you do not want the service to run on a particular instance, select Not Used for the Service Policy.

  7. If you want to modify the connection load balancing goal to distribute the connection workload based on elapsed time instead of the overall number of connections, select Short. Otherwise, leave this setting at the default value of Long.

  8. To enable the Load Balancing Advisory for this service, select Enable Load Balancing Advisory under the heading Notification Properties on the Create Service page, as shown in the following screenshot. Choose a service-level goal of either Service Time or Throughput.

    Description of wlm007.gif follows
    Description of the illustration wlm007.gif

  9. If this service is used by an Oracle Call Interface (OCI) or ODP.NET application, and you want to enable FAN, select Enable Fast Application Notification under the heading Notification Properties.

  10. In the Service Level Thresholds section you can optionally set the service-level thresholds by entering a value in microseconds for Warning and Critical thresholds for the Elapsed Time and CPU Time metrics.

  11. If you want to use a Resource Plan to control the resources used by this service, select the name of the consumer group from the Consumer Group Mapping list in the Resource Management Properties section. For example, you might choose the LOW_GROUP consumer group to give development users low priority to database resources.


    You cannot change the consumer group name for a service on the Edit Service page. This is because there may be several consumer groups associated with a given service. However, the Edit Service page contains a link to the Resource Consumer Group Mapping page, where you can modify the consumer group mapping for the service.
  12. If this service is used by a specific Oracle Scheduler job class, you can specify the mapping by selecting the name from the Job Scheduler Mapping list in the Resource Management Properties section.

  13. Click OK to create the service.

See Also:

Oracle Database Administrator's Guide for more information about the Oracle Scheduler and job classes

Configuring Oracle Net to Support Services

Although Enterprise Manager configures Oracle Clusterware resources for the newly created service, it does not generate the corresponding entries in your tnsnames.ora file.

To configure Oracle Net Services to support the newly created service:

  1. Determine if the Listener on the local node recognizes the new service by using the following command:

    lsnrctl status

    You should see a list for the new service, similar to the following:

    Service "DEVUSERS.oracle.com" has 1 instance(s).
      Instance "sales1", status READY, has 2 handler(s) for this service...

    The displayed name for your newly created service, for example DEVUSERS.oracle.com, is the value you will use for the SERVICE_NAME parameter in the tnsnames.ora file.

  2. Use a text editor to modify the tnsnames.ora file in the $ORACLE_HOME/network/admin directory on each node that contains an instance listed as a Preferred or Available instance for the service. Add an entry similar to the following, specifying for the VIP address for each node:

        (ADDRESS_LIST = Service
          (ADDRESS = (PROTOCOL = TCP)(HOST = docrac1-vip)(PORT = 1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = docrac2-vip)(PORT = 1521))
        (LOAD_BALANCE = yes)

    In the previous example, the ADDRESS_LIST parameter contains one ADDRESS for each node that contains an instance configured as either Preferred or Available for the service.

  3. Test the Oracle Net Services configuration by attempting to connect to the database using SQL*Plus and the service name, for example:

    $ sqlplus system/oracle@DEVUSERS

    You should see a message indicating you successfully connected to the Oracle database. If you get an error message, examine the tnsnames.ora file and verify the entry was typed in correctly and all the information is correct for your environment.

  4. Repeat these steps on the other nodes in your cluster that contain instances specified as either Preferred or Available for the newly created service.

Administering Services

You can create and administer services with Enterprise Manager or DBCA. You can also use the DBMS_SERVICE PL/SQL package and the SRVCTL utility to perform most service administration tasks.

The following sections describe how to manage services for your cluster database:

Administering Services with Enterprise Manager

The Cluster Managed Database Services page is the master page for beginning all tasks related to services. To access this page, go to the Cluster Database Maintenance page, then click Cluster Managed Database Services in the Services section. You can use this page and links from this page to do the following:

  • View a list of services for the cluster

  • View the instances on which each service is currently running

  • View the status for each service

  • Create or edit a service

  • Start or stop a service

  • Enable or disable a service

  • Perform instance-level tasks for a service

  • Delete a service

Using the Cluster Managed Database Services Page

When managing services using Enterprise Manager, you use the Cluster Managed Database Services page.

To access the Cluster Managed Database Services page:

  1. From the Cluster Database Home page, click the Maintenance tab.

  2. From the Cluster Database Maintenance page, under the Services heading in the High Availability options list, click Cluster Managed Database Services.

    The Cluster and Database Login page appears.

  3. Enter credentials for the database and for the cluster that hosts the cluster database, then click Continue.

    The Cluster Managed Database Services page appears and displays services that are available on the cluster database instances.

On the Cluster Managed Database Services page you can perform the following tasks:

  • View a list of services for the cluster, the instances on which each service is currently running, and the status for each service.

  • Start or stop a service, or enable or disable a service.

  • Access the Create Service and Edit Service pages.

  • Access the Services Detail page to perform instance-level tasks for a service.

  • Test the connection for a service.

Using the Cluster Managed Database Services Detail Page

You access the Cluster Managed Database Services detail page from the Cluster Managed Database Services page by clicking the name of the service you want to view.

On the Cluster Managed Database Services detail page for a service you can perform the following tasks:

  • View the status of a service on all of its preferred and available instances; the status can be Running, Stopped, or Disabled.

  • Stop or start a service for an instance of a cluster database.

  • Disable or enable a service for an instance of a cluster database.

  • Relocate a service to manually rebalance the services across database instances.

Description of wlm008.gif follows
Description of the illustration wlm008.gif

Configuring Service-Level Thresholds

When you create a service, you can specify thresholds for measuring the performance of the service. If the specified threshold value is exceeded by the service, the Automatic Workload Repository (AWR) raises an alert that is displayed by Enterprise Manager.

Performance-related statistics, wait events, and active sessions are monitored at the service level. Also, the AWR enables you to monitor performance using services. It records the service performance, including SQL execution times, wait classes, and resources consumed by a service.

You can specify values for the Elapsed Time Threshold or the CPU Time Threshold when you create a service. You can specify Warning and Critical threshold values for these metrics.

To modify service-level thresholds:

  1. From the Cluster Database Home page, scroll down to the Instances section.

  2. Click the name of the instance for which you want to modify the metric thresholds.

  3. On the Cluster Database Instance Home page, in the Related Links section at the bottom of the page, click Manage Metrics.

  4. Click Edit Thresholds.

  5. Select either the Service Response Time or the Service CPU Time threshold.

  6. Enter a threshold value in microseconds in the Warning Threshold or Critical Threshold columns, then click OK.

Managing Services Using the Database Configuration Assistant

The Oracle Database Configuration Assistant (DBCA) Services Management feature enables you to manage service assignments and service preferences for instances. You can perform these procedures while your Oracle RAC database is running. Even if your instance or the Oracle RAC database is not running, you can still use DBCA to configure services, but the services will not start automatically.

To add, modify, or delete services using the DBCA Services Management feature:

  1. Start the DBCA utility. On Red Hat Linux, the executable file is located in the $ORACLE_HOME/bin directory.

  2. On the DBCA Welcome window, select the Oracle Real Application Clusters database option and click Next.

  3. On the DBCA Operations window, select Services Management and click Next.

  4. If you have not defined any services, the Database Services window appears. In this window, expand the entry for the cluster database for which you want to manage services.

    If have previously created services for at least one database, the List of cluster databases window appears. Select the name of the database for which you want to configure service, then click Next. The Database Services window appears.

  5. Add, remove or modify the services.

    • To add a service, click Add. In the Add a Service window, enter the name of the service, then click OK. Note that service names with the prefix SYS$ are reserved for use by Oracle internal processes.

    • To modify a service, select the available options to configure the service's instance preferences. Assign the service to instances for preferred (normal) and available (recovery) processing. DBCA records your changes when you select another service or proceed to another window. You can also modify the TAF policy for the service.

    • To delete a service, select the service and click Remove.

    Description of dbca01.gif follows
    Description of the illustration dbca01.gif

  6. Click Finish, then click OK to confirm you want to configure the services for the database. DBCA displays a progress indicator while it configures your services. When it has completed, you are asked if you want to perform another operation. Select Yes to return to the Operations page or No to exit DBCA.

When you click Finish, DBCA configures the Oracle Clusterware resources for the services that you added, modified, or removed. DBCA also configures the Oracle Net Services entries for these services and starts them. When you use DBCA to remove services, DBCA stops the service, removes the Oracle Clusterware resource for the service, and removes the Oracle Net Services entries.

See Also:

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about managing services by using the PL/SQL DBMS_SERVICE package procedures or by using the SRVCTL utility

Transparent Application Failover: Oracle By Example Series

When Oracle Net Services establishes a connection to an instance, the connection remains open until the client closes the connection, the instance is shut down, or a failure occurs. If you configure transparent application failover (TAF) for the connection, then Oracle RAC moves the session to a surviving instance when an outage occurs.TAF can restart a query after failover has completed, but for other types of transactions, such as INSERT, UPDATE, or DELETE, the application must rollback the failed transaction and resubmit the transaction. You must reexecute any session customizations, in other words, ALTER SESSION statements, after failover has occurred.

Oracle By Example (OBE) has a series on the High Availability features of Oracle Database 10g Release 2. This OBE shows you how to use Enterprise Manager and PL/SQL to set up TAF in an Oracle RAC environment.To view the Transparent Application Failover OBE tutorial, go to the following URL: