7 Managing Database Workload Using Services

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.

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.

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.

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 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.

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:

   DEVUSERS = 
     (DESCRIPTION = 
       (ADDRESS_LIST = Service
         (ADDRESS = (PROTOCOL = TCP)(HOST = docrac1-vip)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = docrac2-vip)(PORT = 1521))
       (LOAD_BALANCE = yes)
       )
     (CONNECT_DATA = (SERVICE_NAME = DEVUSERS.oracle.com))
      )
   

   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 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 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 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 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.