7 Managing Database Workload Using Services

About Oracle Services

Oracle Database 10g introduced 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 entity for managing applications that compete for the same resources, 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 Database 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 using a service, as opposed to using 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 an Oracle RAC database running on one or more nodes to support multiple applications and mixed workloads with optimal efficiency.

The Database Resource Manager provides the ability to prioritize work within an 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, would get fewer resources, and 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 the DBA determines 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 the DBA 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.

The Database Resource Manager enables you to map a resource consumer group to a service so that users who connect using that service are members of the specified resource consumer group, and thus restricted to the resources available to that resource consumer group.

About Oracle RAC High Availability Framework

The Oracle RAC high availability framework enables Oracle RAC to maintain the database, components, and applications in a running state at all times. If an instance, component, or application 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.

About 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, in order to obtain the best performance based on the workload management directives that you have defined for that service.

The advice given by the Load Balancing Advisory takes into account the power of the server as well as the current workload of the service on the server. Enabling the Load Balancing Advisory helps improve the throughput of applications by not sending work to instances that are overworked, running slowly, not responding, or have failed.

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. Due to the integration with Fast Application Notification (FAN), Oracle integrated clients are more aware of the current status of an Oracle RAC cluster. This prevents client connections from waiting or trying to connect to an instance that is no longer available. Also, when an instance is restarted, Oracle RAC uses FAN to notify the application's connection pool so that the connection pool can create connections to the recently started instance and take advantage of the additional resources that this instance provides.

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 by connections using 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 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 that 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.

You can use Net Configuration Assistant (NETCA) to create a net service name, a simple name for the database service. The net service name resolves to the connect descriptor, which is the network address of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. The client uses a connect descriptor to specify the database or instance to which the client wants to connect.

When a net service name is used, establishing a connection to a database instance takes place by first mapping the net service name to the connect descriptor. This mapped information is stored in one or more repositories of information that are accessed using naming methods. The most commonly used naming method is Local Naming, where the net service names and their connect descriptors are stored in a localized configuration file named tnsnames.ora.

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 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 performance level provided by the database instances as indicated by the Load Balancing 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 illustration, 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 most efficient (best) instance from the connection cache. In Figure 7-1, there are three possible nodes to which the connection can be routed. Instance1, which has the least amount of CPU workload, is currently being assigned about 60 percent of the incoming connections. Instance2, which is currently overloaded, is only being assigned around 10 percent of the incoming connections. Instance3, which has a high workload, is being assigned around 30 percent 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 change occurs regarding the state of the instances 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 that are connected 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, and on each client that uses the service to connect to the database. Add an entry similar to the following, specifying 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 Oracle RAC 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 RAC 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 Oracle RAC 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.

About Service Administration Using 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

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.

To access the Cluster Managed Database Services detail page:

1. On the Cluster Database Home page, click the Availability tab.

2. On the Availability subpage, under the Services heading, 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 Oracle RAC database, then click Continue.

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

4. Click the name of the service for which you want to view the details.

   The Cluster Managed Database Service detail page for that service appears. In the following screenshot, the detail page for the DEVUSERS service is displayed.

Description of the illustration wlm008.gif

See Also:

• "About Service Administration Using Enterprise Manager"

• "About Oracle Services"

• "Creating Services"

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. On 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 threshold values for these metrics.

   The Cluster Database Instance Home page appears.

3. In the Related Links section at the bottom of the page, click Metric and Policy Settings.

   The Metric and Policy Settings page appears.

4. Set the View to All metrics, then locate either the Service Response Time or the Service CPU Time threshold. Click the Edit icon for that threshold.

   The Edit Advanced Settings page appears.

5. Enter a threshold value in milliseconds in the Warning Threshold or Critical Threshold field, then click Continue.

   The Metric and Policy Settings page appears.

6. Edit the threshold values for another metric, or, if done, click OK.

Configuring JDBC Client Failover

Your application can use the JDBC development environment for either thick or thin JDBC clients. You must use the JDBC Implicit Connection Cache to enable the FAN features of Fast Connection Failover and Runtime Connection Load Balancing. This section explains how to configure Java Database Connectivity (JDBC) client failover.

To configure client failover for JDBC clients:

1. Use the Cluster Managed Services page in Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control to create new services. See "Creating Services" for more information about creating services.

2. Enable fast connection failover for JDBC clients by setting the DataSource property FastConnectionFailoverEnabled to TRUE, as in the following example:

   OracleDataSource ods = new OracleDataSource()
   ...
   ods.setUser("hr");
   ods.setPassword("hr_user_password");
   ods.setConnectionCachingEnabled(True);
   ods.setFastConnectionFailoverEnabled(True);
   ods.setConnectionCacheName("MyCache");
   ods.setConnectionCacheProperties(cp);
   

3. Set the oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR property to a nonzero value on the data source (not on the implicit connection cache). When this property is set, if the JDBC client attempts to connect to a host that is unavailable, the connection attempt is bounded to the time specified for oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR. After the specified time has elapsed and a successful connection has not been made, the client attempts to connect to the next host in the address list. Setting this property to a value of 3 seconds is sufficient for most installations.

4. Configure JDBC clients to use a connect descriptor that includes a list of the VIP addresses for each node in the cluster, and that connects to an existing service. The following example is for a two-node Oracle Real Application Clusters (Oracle RAC) cluster:

   Sales_JDBC =
     (DESCRIPTION =
       (ADDRESS=(PROTOCOL=TCP)(HOST=docrac1_vip)(PORT=1521))
       (ADDRESS=(PROTOCOL=TCP)(HOST=docrac2_vip)(PORT=1521))
       (LOAD_BALANCE = yes)
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = Sales_JDBC)
       )
     )
   

   Note:

   Do not configure Transparent Application Failover (TAF) with Fast Connection Failover for JDBC thick clients as TAF processing will interfere with FAN ONS processing.

   If you are using a JDBC thin driver, you must include the complete connect descriptor in the URL because the JDBC thin driver does not use Oracle Net.

5. Configure a remote Oracle Notification Services (ONS) subscription on the JDBC client so that an ONS daemon is not required on the client, as in the following example:

   ods.setONSConfiguration("docrac1_vip:6200,docrac2_vip:6200");
   

   The remote ONS subscription must contain every host that the client application can use for failover. In addition, use Secure Sockets Layer (SSL) for all ONS communications, as in the following example:

   ods.setONSConfiguration("nodes=docrac1_vip:6200,
   docrac2_vip:6200 walletfile=/mydir/conf/Wallet");
   

Configuring OCI Client Failover

This section explains how to configure Oracle Call Interface (OCI) clients for failover using FAN events.

To configure OCI client failover:

1. Use the Cluster Managed Services page in Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control to a create services for the OCI clients. See "Creating Services" for more information about creating services.

   You should configure the primary instance as preferred for that service. Under Service Properties, set the Transparent Application Failover Policy to Basic. For Notification Properties, choose "Enable Fast Application Notification for OCI and ODP.NET Applications".

   See "Administering Services" for more information about modifying services using Enterprise Manager.

2. Enable FAN for OCI clients by initializing the environment with the OCI_EVENTS parameter, as in the following example:

   OCIEnvCreate(...OCI_EVENTS...)
   

3. Link the OCI client applications with thread library libthread or libpthread.

4. After Fast Application Notification has been enabled, clients and applications can register a callback that is invoked whenever a high availability event occurs, as shown in the following example:

   OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *)evtcallback_fn, 
     (ub4) 0, (ub4)OCI_ATTR_EVTCBK, errhp); 
   OCIAttrSet(envhp, (ub4) OCI_HTYPE_ENV, (dvoid *)evtctx, (ub4) 0, 
     (ub4)OCI_ATTR_EVTCTX, errhp);
   

   After registering an event callback and context, OCI will call the registered function once for each high availability event.

Configuring ODP.NET Client Failover

When a connection to an Oracle instance is unexpectedly severed, Transparent Application Failover (TAF) seamlessly attempts to failover to another Oracle instance. Due to the delay that a failover can incur, the application may wish to be notified by a TAF callback. ODP.NET supports TAF callback through the Failover event of the OracleConnection object. To receive TAF callbacks, an event handler function must be registered with the Failover event of the OracleConnection object. Also the connection parameter enlist should be set to false for TAF to work.

The procedures for enabling ODP.NET are similar to the procedures for enabling JDBC in that you must set parameters in the connection string to enable FCF. This section explains how to configure Oracle Data Provider for .NET (ODP.NET) clients for failover using FAN events.

To configure ODP.Net client failover:

1. Use the Cluster Managed Services page in Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control to a create services for the ODP.NET clients. See "Creating Services" for more information about creating services.

   You should configure the primary instance as preferred for that service. Under Service Properties, set the Transparent Application Failover Policy to Basic. For Notification Properties, choose "Enable Fast Application Notification for OCI and ODP.NET Applications". Set the Connection Load Balancing Goal to Long.

2. Enable FCF for ODP.NET connection pools by subscribing to FAN high availability events. Do this by setting the ha events connection string attribute to true at connection time. Note that this only works if you are using connection pools. In other words, do this if you have set the pooling attribute to true, as shown in the following example:

   // C#
   using System;
   using Oracle.DataAccess.Client;
   class HAEventEnablingSample
   {
     static void Main()
     {
       OracleConnection con = new OracleConnection();
   
       // Open a connection using ConnectionString attributes
       // Also, enable "load balancing"
       con.ConnectionString =
         "User Id=scott;Password=password;Data Source=oracle;" +
         "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
         "HA Events=true;Incr Pool Size=5;Decr Pool Size=2";
   
       con.Open();
   
       // Create more connections and carry out work against the DB here.
   
       // Dispose OracleConnection object
       con.Dispose();
       }
    }