6 Introduction to Workload Management

Using Oracle Services

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 group other types of work under services. For example, online users can be a service, batch processing can be another, and reporting can be another service type.

Oracle recommends that all users who share a service have the same service level requirements. You can define specific characteristics for services and each service is a separate unit of work. When you define a service, you define which instances will normally support that service. These are known as the PREFERRED instances. You can also define other instances to support a service if the service's preferred instance fails. These are known as AVAILABLE instances.

When you specify the PREFERED instances, you are specifying the number of instances on which a service will initially run. Afterwards, due to either instance availability or planned service relocations, a service may be running on an AVAILABLE instance. When a service moves to an available instance, Oracle does not move the service back to the PREFERRED instance when the preferred instance comes back online. This is because the service is already running on the desired number of instances. This provides a higher level of continual service availability and avoids a second service outage just for fail back processing. You can, however, easily automate fail back by using Fast Application Notification (FAN) callouts.

Services are integrated with the Resource Manager which enables you to restrict the resources that are used by a service within an instance. The Resource Manager enables you to map a consumer group to a service so that users connecting with the service are put into the specified consumer group. Also, the Automatic Workload Repository (AWR) enables you to monitor performance by service.

Oracle Net Services provides connection load balancing to enable you to spread user connections across all of the instances that are supporting a service. RAC uses FAN to notify applications about configuration changes and the current service level that is provided by each instance where the service is enabled.

The easiest way to use FAN is to use the Oracle Clients that have Fast Connection Failover (FCF), which means the clients have been integrated with the high availability FAN events. These include Java Database Connectivity (JDBC), Oracle Call Interface (OCI), and ODP.NET. OCI clients can include clients with TAF enabled. For OCI and ODP.NET, you must enable the service to send FAN high availability events, in other words, set AQ_HA_NOTIFICATIONS to TRUE.

With runtime connection load balancing, applications can use load balancing advisory events to provide better service to users. The Oracle JDBC and ODP.NET clients are automatically integrated to take advantage of load balancing advisory events. The load balancing advisory informs the client about the current service level that an instance is providing for a service. The load balancing advisory also recommends how much of the workload should be sent to that instance.

Default Service Connections

By default, when you create a database, Oracle defines one service for your RAC database and this service is a special Oracle database service. The default database service is always available on all instances in a RAC environment, unless the instance is in restricted mode. You cannot alter this service or its properties.

The database also supports the following internal services:

• SYS$BACKGROUND is used by the background processes only

• SYS$USERS is the default service for user sessions that are not associated with any application service

Both of these internal services support all of the workload management features. You cannot stop or disable either service.

Connection Load Balancing

Oracle Net Services provides the ability to balance client connections across the instances in a RAC configuration. There are two types of load balancing that you can implement: client side and server side load balancing. Client side load balancing balances the connection requests across the Listeners. With server side load balancing, the Listener directs a connection request to the best instance by using the load balancing advisory. In a RAC database, client connections should use both types of connection load balancing.

FAN, FCF, and the load balancing advisory depend on an accurate connection load balancing configuration that includes setting the connection load balancing goal for the service. You can use a goal of either long or short for connection load balancing. These goals have the following characteristics:

• Long—Use this connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. Long is the default connection load balancing goal. The following is an example of modifying a service, POSTMAN, with the PL/SQL DBMS_SERVICE package and the CLB_GOAL_LONG package constant to define the connection load balancing goal for long-lived sessions:

  execute dbms_service.modify_service (service_name => 'POSTMAN' -
          , clb_goal => dbms_service.clb_goal_long);
  
  

• Short—Use this connection load balancing method for applications that have short-lived connections. The following example modifies the service known as ORDER, using the DBMS_SERVICE.CLB_GOAL_SHORT package constant to set the goal to short:

  execute dbms_service.modify_service (service_name => 'ORDER' -
          , clb_goal => dbms_service.clb_goal_short);
  
  

When you create a RAC database with the Database Configuration Assistant (DBCA), the DBCA configures and enables server side load balancing by default. The DBCA also creates a sample client side load balancing connection definition in the tnsnames.ora file on the server. Any services created through the DBCA have the default settings of GOAL=NONE and CLB_GOAL=CLB_GOAL_LONG.

When Oracle Net Services establishes a connection to an instance, the connection remains open until the client closes the connection, the instance is shutdown, or a failure occurs. If you configure transparent application failover (TAF) for the connection, then Oracle 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 re-execute any session customizations, in other words, ALTER SESSION statements, after failover has occurred. However, with TAF, a connection is not moved during normal processing, even if the workload changes over time. The TAF setting on a service overrides any TAF setting at the client connection definition.

Client side load balancing is defined in your client connection definition by setting the parameter LOAD_BALANCE=ON (the default is ON for description lists). When you set this parameter to ON, Oracle randomly selects an address in the address list, and connects to that node's Listener. This provides a balancing of client connections across the available Listeners in the cluster. When the Listener receives the connection request, the Listener connects the user to an instance that the Listener knows provides the requested service. To see what services a Listener supports, run the lsnrctl services command.

Overview of Fast Application Notification

FAN is a notification mechanism that RAC uses to notify other processes about configuration and service level information. RAC also uses FAN to notify applications about service status changes, such as UP or DOWN events. FAN events enable applications to take immediate action. FAN UP and DOWN events refer to instances, services, or nodes.

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

For DOWN events, the disruption to the application can be minimized because sessions to the failed instance or node can be terminated. Incomplete transactions are terminated and the application user is immediately notified. Application users who request connections are directed to available instances only. You can also use FAN to:

• Log status information.

• Page DBAs or open support tickets when resources fail to start.

• Automatically start dependent external applications that need to be co-located with a service.

• Change resource plans or shut down services when the number of available instances decreases, for example, if nodes fail.

• Automate the fail back of a service to PREFERRED instances if needed.

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.

FAN also publishes load balancing advisory events. Applications can take advantage of the load balancing advisory FAN events to direct work requests to the instance in the cluster that is currently providing the best service quality.

FAN events are published using the Oracle Notification Service (ONS) and an Oracle Streams Advanced Queue. The publication mechanisms are automatically configured as part of the RAC installation.

You can take advantage of FAN events in the following three ways:

1. Your application can take advantage of FAN without any programmatic changes by utilizing an integrated Oracle Client. The integrated clients for FAN events include Oracle Database 10g JDBC (Oracle Database 10g Release 2 is required for load balancing), Oracle Database 10g Release 2 Oracle Data Provider for .NET (ODP.NET), and Oracle Database 10g Release 2 Oracle Call Interface (OCI). This includes applications that use TAF.

2. Applications can take advantage of FAN programmatically by using the ONS Application Programming Interface (API) to subscribe to FAN events and execute event handling actions upon the receipt of an event.

3. You can implement FAN with server side callouts on your database tier.

The Connection Manager (CMAN) and Oracle Net Services Listeners also receive FAN event information. This enables the Listener and CMAN to immediately de-register services provided by the failed instance and to avoid erroneously sending connection requests to failed instances. The Listener uses the load balancing advisory when load balancing connection requests where the service has CLB_GOAL=CLB_GOAL_SHORT.

The Connection Manager (CMAN) and Oracle Net Services Listeners also receive FAN event information. This enables the Listener and CMAN to immediately de-register services that are provided by the failed instance. It also avoids erroneously sending connection requests to failed instances. When you use the service goal CLB_GOAL_SHORT, then the Listener uses the load balancing advisory when the Listener balances the connection loads. When load balancing advisory is enabled, the metrics used for the Listener are finer grained.

Application High Availability with Services and FAN

Oracle focuses on maintaining service availability. In RAC, Oracle services are designed to be continuously available with loads shared across one or more instances. The RAC high availability framework maintains the service availability by using the Oracle Clusterware and resource profiles.

Resource profiles are automatically created when you define a service. A resource profile describes how the Oracle Clusterware should manage the service and which instance the service should failover to if the instance that the service is running on fails. Resource profiles also define service dependencies on the instance and the database. Due to these dependencies, if you stop a database, then the instances and services are automatically stopped in the correct order.

The RAC high availability framework monitors the database and its services and sends event notifications using FAN. The Oracle Clusterware recovers and balances services according to business rules. For example, Oracle clients such as the Oracle JDBC driver will redistribute connections in the connection pool when an outage occurs, when a system is restarted, or when additional capacity becomes available.

Managing Unplanned Outages

You can assign services in RAC to one or more instances. If RAC detects an outage, then the Oracle Clusterware isolates the failed component and recovers the dependent components. For services, if the failed component is an instance, then the Oracle Clusterware relocates the service to an available instance in the cluster. FAN events can occur at various levels within the Oracle Server architecture and the response can include notifying external entities by way of ONS, Advanced Queuing, or the use of FAN callouts.

Notification occurs from a surviving node when the failed node is out of service. The location and number of instances in a RAC environment that provide a service are transparent to applications. Restart and recovery are automatic, including the restarting of the subsystems, such as the Listener and the Automatic Storage Management (ASM) processes, not just the database. You can use FAN callouts to report faults to your fault management system or to initiate repair jobs.

Managing Planned Outages

For repairs, upgrades, and changes that require you to isolate one or more instances, RAC provides interfaces that relocate, disable, and enable services to minimize service disruption to application users. Once you complete the operation, you can return the service to normal operation.

Remember that due to dependencies, if you manually shutdown your database, then all of your services will be automatically stopped. If you then manually restart the database, then you must also restart the services. Use FAN callouts to automate starting the services when the database starts.

Fast Application Notification High Availability Events

Table 6-1 describes the FAN event record parameters and the event types, followed by name-value pairs. The event type is always the first entry and the timestamp is always the last entry as in the following example:

FAN event type: service_member
Properties: version=1.0 service=ERP database=FINPROD instance=FINPROD3 host=node3 status=up

A FAN record matches the database signature of each session as shown in Table 6-2. The signature information is also available using OCI_ATTRIBUTES. These attributes are available in the OCI Connection Handle. Use this information to take actions on sessions that match the FAN event data.

Using Fast Application Notification Callouts

FAN callouts are server side executables that RAC executes immediately when high availability events occur. You can use FAN callouts to automate activities when events occur in a cluster configuration, such as:

• Opening fault tracking tickets

• Sending messages to pagers

• Sending e-mail

• Starting and stopping server side applications

• Maintaining an uptime log by logging each event as it occurs

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

To use FAN callouts, place an executable in the directory CRS_home/racg/usrco on every node that runs the Oracle Clusterware. If you are using scripts, then set the shell as the first line of the executable. The following is an example file for the CRS_home/racg/usrco/callout.sh callout:

#! /bin/ksh
FAN_LOGFILE= [your path name]/admin/log/`hostname`_uptime.log
echo $* "reported="`date` >> $FAN_LOGFILE &

The following output is from the previous example:

NODE VERSION=1.0 host=sun880-2 incarn=23 status=nodedown reason= timestamp=08-Oct-2004 04:02:14 reported=Fri Oct 8 04:02:14 PDT 2004

A FAN record matches the database signature of each session, as shown in Table 6-2. The signature information is also available using OCI_ATTRIBUTES. These attributes are available in the OCI Connection Handle. Use this information to take actions on sessions matching the FAN event data.

Overview of the Load Balancing Advisory

Load balancing distributes work across all available RAC database instances. Oracle recommends that applications use persistent connections that span the instances that offer a service. Connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exist for relatively short duration. The load balancing advisory has the task of advising the direction of incoming work to the RAC instances that provide optimal quality of service for that work.

The load balancing advisory directs the work requests to the best RAC instances to serve the work which minimizes the need to relocate the work later. By using the THROUGHPUT or SERVICE_TIME goals, feedback is built in to the system. The work is routed to provide the best service times globally, and routing responds gracefully to changing conditions in the system. In steady state, the system approaches equilibrium with improved throughput across all of the RAC instances.

Standard architectures that can use the load balancing advisory include connection load balancing, TP monitors, application servers, connection concentrators, hardware and software load balancers, job schedulers, batch schedulers, and message queuing systems. All of these applications can allocate work.

The load balancing advisory is deployed with key Oracle clients, such as Listener, JDBC Implicit Connection Cache 10g, and ODP.NET Connection Pool. The Load Balancing Advisory is also open for third party subscription by way of ONS.

Administering the Load Balancing Advisory

You can configure the load balancing advisory by defining service-level goals for each service that you want to balance. This enables the RAC load balancing advisory for that service and FAN load balancing events are published. There are two types of service-level goals for runtime:

• SERVICE TIME—Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE TIME is for workloads such as internet shopping where the rate of demand changes:

  execute dbms_service.modify_service (service_name => 'OE' 
  , goal => dbms_service.goal_service_time -
  , clb_goal => dbms_service.clb_goal_short);
  
  

• THROUGHPUT—Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. An example for the use of THROUGHPUT is for workloads such as batch processes, where the next job starts when the last job completes:

  execute dbms_service.modify_service (service_name => 'sjob' -
          , goal => dbms_service.goal_service_time -
         , clb_goal => dbms_service.clb_goal_long);
  
  

  Setting the goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary and in the views DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES.

  
  

  See Also: "Administering Services" for more information about administering services and adding goals to services

  
  

Load Balancing Advisory FAN Events

The load balancing advisory FAN events provide metrics for load balancing algorithms. The easiest way to take advantage of these events is to use an Oracle integrated client such as ODP.NET or JDBC. Client applications can subscribe to these events directly by way of the ONS Application Programming interface. Table 6-3 describes the load balancing advisory event parameters.

Use the following example to monitor the load balancing advisory events:

set pages 60 space 2 lines 132 num 8 verify off feedback off
column user_data heading "AQ Service Metrics" format A60 wrap
break on SERVICE_NAME skip 1
SELECT
 to_char(ENQ_TIME, 'HH:MI:SS') Enq_time
 , user_data
from SYS.SYS$SERVICE_METRICS_TAB
order by 1 ;

Enabling Oracle Call Interface to Receive FAN High Availability Events

Oracle Call Interface (OCI) clients can register to receive notifications about RAC high availability events and respond when events occur. This improves the session failover response time in OCI and also removes terminated connections from connection and session pools. This feature works on OCI applications that use TAF, connection pools, or session pools.

First, you must enable a service for high availability events. If your application is using TAF, then enable the TAF settings for the service. Then configure client applications to enable them to connect to a RAC instance to enable event notification. Clients can register callbacks that are used whenever an event occurs. This reduces the time that it takes to detect a connection failure. During DOWN event processing, OCI:

• Terminates affected connections at the client.

• Removes connections from the OCI connection pool and the OCI session pool—the session pool maps each session to a physical connection in the connection pool, and there can be multiple sessions for each connection

• Fails over the connection if you have configured TAF.

If TAF is not configured, then the client only receives an error.

Perform the following steps to configure Advanced Queueing notifications to an OCI client:

1. Ensure that the service that you are using has Advanced Queuing notifications enabled by setting the services' values of AQ_HA_NOTIFICATIONS to TRUE. For example:

   execute dbms_service.modify_service (service_name => 'gl.us.oracle.com' -        , aq_ha_notifications => true -        , failover_method  => dbms_service.failover_method_basic  -        , failover_type    => dbms_service.failover_type_select  -        , failover_retries => 180  -        , failover_delay   => 5  -        , clb_goal => dbms_service.clb_goal_long);
   
   

2. Enable OCI_EVENTS at environment creation time on the client as follows:

   ( OCIEnvCreate(...) )
   
   

3. Client applications must link with the client thread or operating system library.

Clients on which you want to use this functionality that also require notifications, including TAF clients, must:

• Register for notifications

• Optionally register a client EVENT callback

An Advanced Queueing ALERT_QUE is populated automatically. To see the alert information, you can query the views DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY. OCI clients obtain a subscription to this queue if they meet the three criteria specified previously in this section.

Enabling Java Database Connectivity to Receive FAN Events

Enabling FAN for the Oracle JDBC Implicit Connection Cache enables FAN high availability events in Oracle Database 10g release1, and the load balancing advisory in Oracle Database 10g release 2. Your application can use the JDBC development environment for either thick or thin JDBC clients to take advantage of FAN. For FAN, your application must use the JDBC implicit connection cache.

To configure the JDBC client, set the FastConnectionFailoverEnabled property before making the first getConnection() request to a data source. When you enable FCF, the failover applies to every connection in the connection cache. If your application explicitly creates a connection cache using the Connection Cache Manager, then you must first set FastConnectionFailoverEnabled.

Enabling ODP.NET to Receive FAN High Availability Events

Oracle Data Provider for .NET (ODP.NET) connection pools can subscribe to notifications that indicate when nodes, services, and service members are down. After a DOWN event, Oracle cleans up sessions in the connection pool that go to the instance that stops and ODP.NET proactively disposes connections that are no longer valid. ODP.NET establishes connections to existing RAC instances if the removal of severed connections brings the total number of connections below the value that is set for the MIN POOL SIZE parameter.

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. Perform the following steps to enable FAN:

1. Enable Advanced Queuing notifications by using the DBMS.SERVICE package as in the following example:

2. execute dbms_service.modify_service (service_name => 'gl.us.oracle.com' -        , aq_ha_notifications => true -        , failover_method  => dbms_service.failover_method_basic  -        , failover_type    => dbms_service.failover_type_select  -        , failover_retries => 180  -        , failover_delay   => 5  -        , clb_goal => dbms_service.clb_goal_long);
   
   

3. 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. You can do this at connection time. Note that this only works if you are using connection pools. In other words, if you have set the pooling attribute to true, which is the default. The following example describes this in detail:

   // 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=tiger;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();
     }
   }
   
   

Enabling ODP.NET to Receive FAN Load Balancing Advisory Events

Use the following procedures to enable ODP.NET to receive FAN load balancing advisory events:

1. Enable Advanced Queuing notifications by using the DBMS.SERVICE package as in the following example:

   execute dbms_service.modify_service (service_name => 'gl.us.oracle.com' -        , aq_ha_notifications => true -        , failover_method  => dbms_service.failover_method_basic  -        , failover_type    => dbms_service.failover_type_select  -        , failover_retries => 180  -        , failover_delay   => 5  -        , clb_goal => dbms_service.clb_goal_long);
   
   

2. Set the GOAL and CLB_GOAL for the service and ensure that Oracle Net Service is configured for connection load balancing.

3. To take advantage of load balancing events with ODP.NET connection pools, set the load balancing string to TRUE; the default is FALSE. You can do this at connect time. This only works if you are using connection pools, or when the pooling attribute is set to TRUE which is the default, as in the following example:

   // C#
   using System;
   using Oracle.DataAccess.Client;
    
   class LoadBalancingEnablingSample
   {
     static void Main()
     {
       OracleConnection con = new OracleConnection();
    
       // Open a connection using ConnectionString attributes
       // Also, enable "load balancing"
       con.ConnectionString =
         "User Id=scott;Password=tiger;Data Source=oracle;" +
         "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
         "Load Balancing=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();
     }
   }
   
   

Enabling Distributed Transaction Processing for Services

For services that you are going to use for distributed transaction processing, create the service using Enterprise Manager, the DBCA, or SRVCTL and define only one instance as the preferred instance. You can have as many AVAILABLE instances as you want. For example, the following SRVCTL command creates a singleton service for database crm, xa_01.service.us.oracle.com, whose preferred instance is RAC01:

srvctl add service -d crm -s xa_01.service.us.oracle.com -r RAC01 -a RAC02, RAC03 

Then mark the service for distributed transaction processing by setting the DTP parameter to TRUE; the default is FALSE. Enterprise Manager enables you to set this parameter on the Cluster Managed Database Services: Create Service or Modify Service page. You can also use the DBMS_SERVICE package to modify the DTP property of the singleton service as follows:

execute dbms_service.modify_service(service_name =>'xa_01.service.us.oracle.com', DTP=>TRUE); 

If, for example, RAC01 which provides service XA_01 fails, then the singleton service that it provided fails over to one of the other instances, such as RAC02 or RAC03.

If services have migrated to other instances since the cold-start of the RAC database, then you might need to force the relocation of the service to evenly re-balance the load on all of the available hardware. Use the GV$ACTIVE_SERVICES view to determine whether to do this.

Administering Services with Enterprise Manager

The Cluster Managed Database Services page is the master page for initiating 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 drilldowns from this page to:

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

Administering Services with the Database Configuration Assistant

You can administer services in RAC with the DBCA as described under the following topics:

• Using the Database Configuration Assistant to Add and Modify Services

• Using the Database Configuration Assistant to Administer Services

Administering Services with the PL/SQL DBMS_SERVICE Package

You can manage services with the PL/SQL DBMS_SERVICE package procedures. These procedures are described in the following sections:

• CREATE_SERVICE

• MODIFY_SERVICE

• DELETE_SERVICE

• START_SERVICE

• STOP_SERVICE

• DISCONNECT_SESSION

Administering Services with SRVCTL

When you create a service with SRVCTL, you must start it with a separate SRVCTL command. However, you may later need to manually stop or restart the service. You may also need to disable the service to prevent automatic restarts, to manually relocate the service, or obtain status information about the service. The following sections explain how to use SRVCTL to perform the following administrative tasks:

• Creating Services with SRVCTL

• Starting and Stopping Services with SRVCTL

• Enabling and Disabling Services with SRVCTL

• Relocating Services with SRVCTL

• Obtaining the Statuses of Services with SRVCTL

• Obtaining the Configuration of Services with SRVCTL

srvctl add service -d database_unique_name -s service_name -r preferred_list[-a available_list] [-P TAF_policy]

srvctl start service -d database_unique_name [-s service_name_list] [-i inst_name]  [-o start_options] [-c connect_str | -q]

srvctl stop service -d database_unique_name -s service_name_list [-i inst_name]  [-o start_options] [-c connect_str | -q]

srvctl enable service -d database_unique_name -s service_name_list [-i inst_name]

srvctl disable service -d database_unique_name -s service_name_list [-i inst_name]

srvctl relocate service -d apps -s crm -i apps1 -t apps3

srvctl status service -d apps -s crm

srvctl config service -d apps -s crm -a

Service Thresholds and Alerts

Service level thresholds enable you to compare achieved service levels against accepted minimum required levels. This provides accountability with respect to the delivery or the failure to deliver an agreed service level. The end goal is a predictable system that achieves service levels. There is no requirement to perform as fast as possible with minimum resource consumption; the requirement is to meet the quality of service.

You can explicitly specify two performance thresholds for each service: the response time for calls, or SERVICE_ELAPSED_TIME, and the CPU time for calls, or SERVICE_CPU_TIME. The response time goal indicates that the elapsed time should not exceed a certain value, and the response time represents wall clock time. Response time is a fundamental measure that reflects all delays and faults that might be blocking the call from running on behalf of the user. Response time can also indicate differences in node power across the nodes of a RAC database. The service time and CPU time are calculated as the moving average of the elapsed, server side call time. The AWR monitors the service time and CPU time and publishes AWR alerts when the performance exceeds the thresholds. You can then respond to these alerts by changing the priority of a job, stopping overloaded processes, or by relocating, expanding, shrinking, starting or stopping a service. This permits you to maintain service availability despite changes in demand.

execute DBMS_SERVER_ALERT.SET_THRESHOLD(        -
METRICS_ID => DBMS_SERVER_ALERT.ELAPSED_TIME_PER_CALL   -
, WARNING_OPERATOR => dbms_server_alert.operator_ge     -
, WARNING_VALUE => '500000'     -
, CRITICAL_OPERATOR => dbms_server_alert.operator_ge    -
, CRITICAL_VALUE => '750000'    -
, OBSERVATION_PERIOD => 30      -
, CONSECUTIVE_OCCURRENCES => 5  -
, INSTANCE_NAME => NULL -
, OBJECT_TYPE => dbms_server_alert.object_type_service  -
, OBJECT_NAME => 'servall');

SELECT METRICS_NAME, INSTANCE_NAME, WARNING_VALUE, CRITICAL_VALUE, OBSERVATION_PERIOD from dba_thresholds ;

execute DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'ERP', MODULE_NAME=> 'PAYROLL', ACTION_NAME => 'EXCEPTIONS PAY');
execute DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'ERP', MODULE_NAME => 'PAYROLL', ACTION_NAME => null);
execute DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'HOT_BATCH', MODULE
_NAME =>'POSTING', ACTION_NAME => null); 

col AGGREGATION_TYPE format a21 trunc heading 'AGGREGATION'
col PRIMARY_ID format a20 trunc heading 'SERVICE'
col QUALIFIER_ID1 format a20 trunc heading 'MODULE'
col QUALIFIER_ID2 format a20 trunc heading 'ACTION'
select * from DBA_ENABLED_AGGREGATIONS ; 

AGGREGATION            SERVICE                MODULE        ACTION
------------           --------------------   ----------    -------------
SERVICE_MODULE_ACTION  ERP                    PAYROLL       EXCEPTIONS PAY
SERVICE_MODULE_ACTION  ERP                    PAYROLL
SERVICE_MODULE_ACTION  HOT_BATCH              POSTING