How To Create Service In Oracle 12c Rac

Oracle Services

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

It is recommended that all users who share a service have the same service-level requirements. You can define specific characteristics for services and each service can be a separate unit of work. There are many options that you can take advantage of when using services. Although you do not have to implement these options, using them helps optimize application performance. You can define services for both policy-managed and administrator-managed databases.

Do not use DBMS_SERVICE with cluster-managed services. When Oracle Clusterware starts a service, it updates the database with the attributes stored in the CRS resource. If you use DBMS_SERVICE to modify the service and do not update the CRS resource, the next time CRS resource is started, it will override the database attributes set by DBMS_SERVICE.

Service Usage in an Oracle RAC Database

Services provide location transparency. A service name can identify multiple database instances, and an instance can belong to multiple services. Several database features use services for an Oracle RAC database. Resource profiles are automatically created when you define a service. A resource profile describes how Oracle Clusterware should manage the service and which instance the service should failover to if the preferred instance stops. Resource profiles also define service dependencies for 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.

Services are integrated with Oracle Resource Manager, which enables you to restrict the resources that users use to connect to an instance by using a service. Oracle Resource Manager enables you to map a consumer group to a service so that users who connect to an instance using that service are members of the specified consumer group. Oracle Resource Manager operates at an instance level.

The metric data generated by AWR is organized into various groups, such as event, event class, session, service, and tablespace metrics. Typically, you view the AWR data by using Oracle Enterprise Manager or AWR reports. In a Global Data Services environment, the RAC service model is applied to sets of globally distributed databases. GDS works with single instance or RAC databases by using Data Guard, GoldenGate, or other replication technologies.

Parallel Operations and Services

By default, in an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster. For this cross-node or inter-node parallel execution to perform well, the interconnect in the Oracle RAC environment must be sized appropriately because inter-node parallel execution may result in a lot of interconnect traffic. To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment by using the PARALLEL_FORCE_LOCAL initialization parameter. By setting this parameter to TRUE, the parallel execution servers can only execute on the same Oracle RAC node where the SQL statement was started.

Services are used to limit the number of instances that participate in a parallel SQL operation. When the default database service is used, the parallel SQL operation can run on all available instances. You can create any number of services, each consisting of one or more instances. When a parallel SQL operation is started, the parallel execution servers are only spawned on instances that offer the specified service used in the initial database connection.

INSTANCE_GROUPS is a RAC parameter that you can specify only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP parameter, it lets you restrict parallel query operations to a limited number of instances. To restrict parallel query operations to a limited number of instances, set the PARALLEL_INSTANCE_GROUP initialization parameter to the name of a service. This does not affect other parallel operations such as parallel recovery or the processing of GV$ queries.

Service-Oriented Buffer Cache Access

Cluster-managed services are used to allocate workloads across various Oracle RAC database instances running in a cluster. These services are used to access database objects cached in the buffer caches of the respective database instances. Service-oriented buffer cache access optimization allows Oracle RAC to cache or pre-warm instances with data blocks for objects accessed through a service. This feature improves access time of Oracle RAC Database instances.

Service-oriented buffer cache access improves performance by managing data with the service to which the data belongs. Access of an object, over time, through a service is mapped and persisted to the database, and this information can be used to improve performance. Blocks that are accessed through the service are cached in the instances where the services are running and, more importantly, the information is not cached where the services are not running.

This information can also be used to pre-warm the cache prior to a singleton service starting. The service start-up can be triggered either by instance start-up or by service relocation. Service-oriented buffer cache access provides consistent performance to any user of that service because the blocks that the service user accesses are cached in the new relocated instance.

Service Characteristics

When you create new services for your database, you should define the automatic workload management characteristics for each service. The characteristics of a service include:

Service Name: The service name is used by clients to connect to one or more instances. The service name must be unique throughout your system.
Service Edition: Edition-based redefinition of database objects enables you to upgrade an application's objects while the objects are in use. When the service edition is set, connections that use this service use this edition as the initial session edition. If the service does not specify the edition name, then the initial session edition is the database default edition.
Service Management Policy: When you use Clusterware to manage your database, you can configure startup options for each individual database service when you add the service by using the srvctl add service command with the -policy parameter. If you set the management policy for a service to AUTOMATIC (the default), then the service starts automatically.
Database Role for a Service: If you configured Oracle Data Guard in your environment, then you can define a role for each service using SRVCTL with the -l parameter.
Instance Preference: When you define a service for an administrator-managed database, you define which instances support that service using SRVCTL with the –preferred parameter. These are known as the preferred instances.
Server Pool Assignment: When you define services for a policy-managed database, you assign the service to a server pool in which the database is hosted using SRVCTL with the –serverpool parameter. You can define the service as either UNIFORM (running on all instances in the server pool) or SINGLETON (running on only one instance in the server pool) using the -cardinality parameter. For singleton services, Oracle RAC chooses on which instance in the server pool the service is active. If that instance fails, then the service fails over to another instance in the server pool. A service can only run in one server pool and Oracle recommends that every server pool has at least one service.
Load Balancing Advisory Goal for Run-time CLB: With run-time connection load balancing, applications can use load balancing advisory events to provide better service to users. Oracle JDBC, Oracle UCP for Java, OCI session pool, ODP.NET, and Oracle WebLogic Server Active GridLink for Oracle RAC clients are automatically integrated to take advantage of load balancing advisory events. To enable the load balancing advisory, use SRVCTL with the -rlbgoal parameter when creating or modifying the service. The load balancing advisory also recommends how much of the workload should be sent to that instance. The goal determines whether connections are made to the service based on best service quality (how efficiently a single transaction completes) or best throughput (how efficiently a complete job or long-running query completes).
Connection Load Balancing Goal: Oracle Net Services provides connection load balancing to enable you to spread user connections across all of the instances that are supporting a service. For each service, you can use SRVCTL to define the method you want the listener to use for load balancing by setting the connection load balancing goal, specified with the -clbgoal parameter. Connections are classified as LONG (such as connection pools and SQL*FORMS), which tells the listener to use session count, or SHORT, which tells the listener to use response-time or throughput statistics. If the load balancing advisory is enabled, then its information is used to balance connections; otherwise, CPU utilization is used to balance connections

Default Service Connections

Your RAC database includes an Oracle database service identified by DB_ UNIQUE_NAME, if set, or DB_NAME or PDB_NAME, if not. This default service is always available on all instances in an Oracle RAC environment, unless an instance is in restricted mode. You cannot alter this service or its properties. In a multi-tenant environment, every PDB has a default service created at PDB creation. Additionally, the database supports the following two 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.

All of these services are used for internal management. You cannot stop or disable any of these internal services to do planned outages or to fail over to Oracle Data Guard. Do not use these services for client connections. You can explicitly manage only the services that you create. If a feature of the database creates an internal service, you cannot manage it using the methods presented here.

Restricted Service Registration

Security is a high priority to all enterprises, and network security and controlling access to the database is a critical component of overall security endeavors. This feature allows listener registration only from local IP addresses, by default, and provides the ability to configure and dynamically update a set of IP addresses or subnets from which registration requests are allowed by the listener. Database Instance registration with a listener succeeds only when the request originates from a valid node. The network administrator can specify a list of valid nodes, excluded nodes, or disable valid node checking. The list of valid nodes explicitly lists the nodes and subnets that can register with the database.

The list of excluded nodes explicitly lists the nodes that cannot register with the database. The control of dynamic registration results in increased manageability and security of Oracle RAC deployments. By default, valid node checking for registration (VNCR) is enabled. In the default configuration, registration requests are only allowed from nodes within the cluster, because they are redirected to the private subnet, and only nodes within the cluster can access the private subnet. Non-SCAN listeners only accept registration from instances on the local node. You must manually include remote nodes or nodes outside the subnet of the SCAN listener on the list of valid nodes by using the registration_invited_nodes_alias parameter in the listener.ora file or by modifying the SCAN listener by using SRVCTL as follows:

$ srvctl modify scan_listener -invitednodes node_list -invitedsubnets subnet_list

Creating Service with Enterprise Manager

From your Cluster Database home page, click the Availability link, and then select Cluster Managed Database Services from the pull-down list. On the Cluster Managed Database Services page, click Create Service. Use the Create Service page to configure a new service in which you do the following:

Select the desired service policy for each instance configured for the cluster database.
Select the desired service properties

If your database is administration managed, the High Availability Configuration section allows you to configure preferred and available servers. If your database employs policy-managed administration, you can configure the service cardinality to be UNIFORM or SINGLETON and assign the service to a server pool.

You can also define the management policy for a service. You can choose either an automatic or a manual management policy.

Automatic: The service always starts when the database starts.
Manual: Requires that the service be started manually. Prior to Oracle RAC 11g Release 2, all services worked as though they were defined with a manual management policy.

Note: Enterprise Manager now generates the corresponding entries in your tnsnames.ora files for your services. Just click the "Update local naming parameter (tnsnames.ora) file" check box when creating the service.

Creating Services with SRVCTL

To create a service called GL with preferred instance RAC02 and an available instance RAC01:

$ srvctl add service –db PROD1 –service GL -preferred RAC02 -available RAC01

To create a service called AP with preferred instance RAC01 and an available instance RAC02:

$ srvctl add service –db PROD1 –service AP –preferred RAC01 -available RAC02

Create a SINGLETON service called BATCH using server pool SP1 and a UNIFORM service called ERP using pool SP2:

$ srvctl add service -db PROD2 -service                  BATCH                  -serverpool SP1 -cardinality singleton -policy manual $ srvctl add service -db PROD2 -service                  ERP                  -serverpool SP2 -cardinality UNIFORM -policy manual

For the example shown above, assume a two-node, administration-managed database called PROD1 with an instance named RAC01 on one node and an instance called RAC02 on the other. Two services are created, AP and GL, to be managed by Oracle Clusterware. The AP service is defined with a preferred instance of RAC01 and an available instance of RAC02.

If RAC01 dies, the AP service member on RAC01 is restored automatically on RAC02. A similar scenario holds true for the GL service. Next, assume a policy-managed cluster database called PROD2. Two services are created, a SINGELTON service called BATCH and a UNIFORM service called ERP. SINGLETON services run on one of the active servers and UNIFORM services run on all active servers of the server pool. The characteristics of the server pool determine how resources are allocated to the service.

Note: When services are created with srvctl, tnsnames.ora is not updated and the service is not started.

Managing Services with Enterprise Manager

You can use Enterprise Manager to manage services within a GUI framework. The screenshot above shows the main page for administering services within RAC. It shows you some basic status information about a defined service. To access this page, select Cluster Managed Database Services from the Availability pull-down menu.

You can perform simple service management such as enabling, disabling, starting, stopping, and relocating services. All possible operations are shown above. If you choose to start a service on the Cluster Managed Database Services page, then EM attempts to start the service on every preferred instance. Stopping the service stops it on all instances that it is currently running.

To relocate a service, select the service that you want to administer, select the Manage option from the Actions drop-down list, and then click Go.

Note: On the Cluster Managed Database Services page, you can test the connection for a service.

Managing Services with EM

To access the Cluster Managed Database Service page for an individual service, you must select a service from the Cluster Managed Database Services page, select the Manage option from the Actions drop-down list, and then click Go. This is the Cluster Managed Database Service page for an individual service. It offers you the same functionality as the previous page, except that actions performed here apply to specific instances of a service.

This page also offers you the added functionality of relocating a service to an available instance. Relocating a service from one instance to another stops the service on the first instance and then starts it on the second.

Managing Services with srvctl

Start a named service on all configured instances:

$ srvctl start service –db orcl –service AP

Stop a service:

$ srvctl stop service –db orcl –service AP –instance orcl4

Disable a service at a named instance:

$ srvctl disable service –db orcl –service AP –instance orcl4

Set an available instance as a preferred instance:

$ srvctl modify service –db orcl –service AP -instance orcl5 –preferred

Relocate a service from one instance to another:

$ srvctl relocate service –db orcl –service AP -oldinst orcl5 –newinst orcl4

The list above demonstrates some management tasks with services by using SRVCTL. Assume that an AP service has been created with four preferred instances: orcl1, orcl2, orcl3, and orcl4. An available instance, orcl5, has also been defined for AP.

In the first example, the AP service is started on all instances. If any of the preferred or available instances that support AP are not running but are enabled, then they are started.

The stop command stops the AP service on instance orcl4. The instance itself is not shut down, but remains running possibly supporting other services. The AP service continues to run on orcl1, orcl2, and orcl_3. The intention might have been to perform maintenance on orcl4, and so the AP service was disabled on that instance to prevent automatic restart of the service on that instance. The OCR records the fact that AP is disabled for orcl4. Thus, Oracle Clusterware will not run AP on orcl4 until the service is enabled.

The next command shown above changes orcl5 from being an available instance to a preferred one. This is beneficial if the intent is to always have four instances run the service because orcl4 was previously disabled. The last example relocates the AP service from instance orcl5 to orcl4. Do not perform other service operations while the online service modification is in progress. The following command relocates the AP service from host01 to host03 using node syntax:

$ srvctl relocate service -db orcl -service AP -currentnode host01 -targetnode node3

Using Services with Client Applications

– Using SCAN:

ERP=(DESCRIPTION=    (LOAD_BALANCE=on)      (ADDRESS=(PROTOCOL=TCP)(HOST=cluster01-scan)(PORT=1521))     (CONNECT_DATA=(SERVICE_NAME=ERP)))

– Using VIPs:

ERP=(DESCRIPTION=    (LOAD_BALANCE=on)      (ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))      (ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))      (ADDRESS=(PROTOCOL=TCP)(HOST=node3-vip)(PORT=1521))     (CONNECT_DATA=(SERVICE_NAME=ERP)))

– Thick JDBC:

url="jdbc:oracle:oci:@ERP"

– Thin JDBC:

url="jdbc:oracle:thin:@(DESCRIPTION=     (LOAD_BALANCE=on)        (ADDRESS=(PROTOCOL=TCP)(HOST=cluster01-scan)(PORT=1521)))      (CONNECT_DATA=(SERVICE_NAME=ERP)))"

The first example above shows the TNS connect descriptor that can be used to access the ERP service. It uses the cluster's Single Client Access Name (SCAN). The SCAN provides a single name to the clients connecting to Oracle RAC that does not change throughout the life of the cluster, even if you add or remove nodes from the cluster. Clients connecting with SCAN can use a simple connection string, such as a thin JDBC URL or EZConnect, and still achieve the load balancing and client connection failover. The second example uses virtual IP addresses as in previous versions of the Oracle Database.

The third example shows the thick JDBC connection description using the previously defined TNS connect descriptor. The third example shows the thin JDBC connection description using the same TNS connect descriptor as the first example.

                  Note: The LOAD_BALANCE=ON clause is used by Oracle Net to randomize its progress through the protocol addresses of the connect descriptor. This feature is called client connection load balancing.

Services and Connection Load Balancing

Oracle Net Services provides the ability to balance client connections across the instances in an Oracle RAC configuration. You can implement two types of load balancing: client-side and server-side. Client-side load balancing balances the connection requests across the listeners. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service, based on the –clbgoal and -rlbgoal settings for the service. In a RAC database, client connections should use both types of connection load balancing.

FAN, Fast Connection Failover, 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 the LONG 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.
SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. The following example modifies the ORDER service, using srvctl to set the goal to SHORT: 'srvctl modify service -s ORDER -j SHORT'

Services and Transparent Application Failover

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 TAF for the connection, then Oracle Database 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 roll back 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.

Services simplify the deployment of TAF. You can define a TAF policy for a service, and all connections using this service will automatically have TAF enabled. This does not require any client-side changes. The TAF setting on a service overrides any TAF setting in the client connection definition.

To define a TAF policy for a service, the srvctl utility can be used as follows:

srvctl modify service -db crm -service GL  -failovermethod BASIC -failovertype SELECT  -failoverretry 10 -failoverdelay 30

Note: TAF applies only to an admin-managed database and not to policy-managed databases.

Using Services with the Resource Manager

The Database Resource Manager (also called Resource Manager) enables you to identify work by using services. It manages the relative priority of services within an instance by binding services directly to consumer groups. When a client connects by using a service, the consumer group is assigned transparently at connect time. This enables the Resource Manager to manage the work requests by service in the order of their importance.

For example, you define the AP and BATCH services to run on the same instance, and assign AP to a high-priority consumer group and BATCH to a low-priority consumer group. Sessions that connect to the database with the AP service specified in their TNS connect descriptor get priority over those that connect to the BATCH service.

This offers benefits in managing workloads because priority is given to business functions rather than the sessions that support those business functions.

Services and Resource Manager with EM

Enterprise Manager (EM) presents a GUI through the Consumer Group Mapping page to automatically map sessions to consumer groups. You can access this page by selecting Resource Manager from the Cluster Administration pull-down menu and then clicking the Consumer Group Mappings link.

Using the General tabbed page of the Consumer Group Mapping page, you can set up a mapping of sessions connecting with a service name to consumer groups as illustrated in the image above. With the ability to map sessions to consumer groups by service, module, and action, you have greater flexibility when it comes to managing the performance of different application workloads.

Using the Priorities tabbed page of the Consumer Group Mapping page, you can change priorities for the mappings that you set up on the General tabbed page. The mapping options correspond to columns in V$SESSION. When multiple mapping columns have values, the priorities you set determine the precedence for assigning sessions to consumer groups.

Note: You can also map a service to a consumer group directly from the Create Service page as shown in the bottom-left corner of the image.

Using Services with the Scheduler

Just as in other environments, the Scheduler in a RAC environment uses one job table for each database and one job coordinator (CJQ0 process) for each instance. The job coordinators communicate with each other to keep the information current.

The Scheduler can use the services and the benefits they offer in a RAC environment. The service that a specific job class uses is defined when the job class is created. During execution, jobs are assigned to job classes and job classes run within services. Using services with job classes ensures that the work of the Scheduler is identified for workload management and performance tuning. For example, jobs inherit server-generated alerts and performance thresholds for the service they run under.

For high availability, the Scheduler offers service affinity instead of instance affinity. Jobs are not scheduled to run on any specific instance. They are scheduled to run under a service. So, if an instance dies, the job can still run on any other instance in the cluster that offers the service.

Note: By specifying the service where you want the jobs to run, the job coordinators balance the load on your system for better performance.

Services and the Scheduler with EM

To configure a job to run under a specific service, select Oracle Scheduler from the Administration pull-down menu and select the Job Classes link. This opens the Scheduler Job Classes page. On the Scheduler Job Classes page, you can see services assigned to job classes. When you click the Create button on the Scheduler Job Classes page, the Create Job Class page is displayed. On this page, you can enter details of a new job class, including which service it must run under.

Note: Similarly, you can map a service to a job class on the Create Service page as shown at the bottom of the image

Services and the Scheduler with EM 02

After your job class is set up with the service that you want it to run under, you can create the job. To create the job, select Oracle Scheduler from the Administration pull-down menu, and select the Jobs link on the Server page. The Scheduler Jobs page appears, on which you can click the Create button to create a new job. When you click the Create button, the Create Job page is displayed. This page has different tabs: General, Schedule, and Options. Use the General tabbed page to assign your job to a job class

Use the Options page (displayed above) to set the Instance Stickiness attribute for your job. Basically, this attribute causes the job to be load balanced across the instances for which the service of the job is running. The job can run only on one instance. If the Instance Stickiness value is set to TRUE, which is the default value, the Scheduler runs the job on the instance where the service is offered with the lightest load. If Instance Stickiness is set to FALSE, then the job is run on the first available instance where the service is offered.

Note: It is possible to set job attributes, such as INSTANCE_STICKINESS, by using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER PL/SQL package.

Using Distributed Transactions with RAC

A global (XA) transaction can span RAC instances by default, allowing any application that uses the Oracle XA library to take full advantage of the Oracle RAC environment to enhance the availability and scalability of the application. GTXn background processes support XA transactions in an Oracle RAC environment. The GLOBAL_TXN_PROCESSES initialization parameter, which is set to 1 by default, specifies the initial number of GTXn background processes for each Oracle RAC instance. Use the default value for this parameter clusterwide to allow distributed transactions to span multiple Oracle RAC instances. Using the default value allows the units of work performed across these Oracle RAC instances to share resources and act as a single transaction (that is, the units of work are tightly coupled). It also allows 2 phase-commit requests to be sent to any node in the cluster. Tightly coupled XA transactions no longer require the special type of singleton services (that is, Oracle Distributed Transaction Processing [DTP] services) to be deployed on Oracle RAC database. XA transactions are transparently supported on Oracle RAC databases with any type of services configuration.

To provide improved application performance with distributed transaction processing (DTP) in RAC, you may want to take advantage of DTP services or XA affinity. Using DTP services, you can direct all branches of a distributed transaction to a single instance in the cluster. To load balance across the cluster, it is better to have several groups of smaller application servers with each group directing its transactions to a single service, or set of services, than to have one or two larger application servers. DTP or XA affinity is required, if suspending and resuming the same XA branch.

Distributed Transactions and Services

To enhance the performance of distributed transactions, you can use services to manage DTP environments. By defining the DTP property of a service, the service is guaranteed to run on one instance at a time in an Oracle RAC database. All global distributed transactions performed through the DTP service are ensured to have their tightly coupled branches running on a single Oracle RAC instance. This has the following benefits:

The changes are available locally within one Oracle RAC instance when tightly coupled branches need information about changes made by each other.
Relocation and failover of services are fully supported for DTP.
By using more DTP services than there are Oracle RAC instances, Oracle Database can balance the load by services across all the Oracle RAC database instances.

To leverage all the instances in a cluster, create one or more DTP services for each Oracle RAC instance that hosts distributed transactions. Choose one DTP service for one distributed transaction. Choose different DTP services for different distributed transactions to balance the workload among the Oracle RAC database instances.

Because all the branches of a distributed transaction are on one instance, you can leverage all the instances to balance the load of many DTP transactions through multiple singleton services, thereby maximizing application throughput.

An external transaction manager, such as OraMTS, coordinates DTP/XA transactions. However, an internal Oracle transaction manager coordinates distributed SQL transactions. Both DTP/XA and distributed SQL transactions must use the DTP service in Oracle RAC.

To create distributed transaction processing (DTP) services for distributed transaction processing, perform the following steps:

1. Create a singleton service using Oracle Enterprise Manager or SRVCTL. For an administrator-managed database, define only one instance as the preferred instance. You can have as many available instances as you want, for example:

                  $ srvctl add service -db crm -service xa_01.example.com -preferred RAC01 -available RAC02,RAC03

For a policy-managed database, specify the server pool to use, and set the cardinality of the service to SINGLETON, for example:

$ srvctl add service -db crm -service xa_01.example.com -serverpool dtp_pool -cardinality SINGLETON

2. Set the DTP parameter (-dtp) for the service to TRUE (the default value is FALSE). You can use Oracle Enterprise Manager or SRVCTL to modify the DTP property of the singleton service. The following example shows how to modify the xa_01.example.com service by using SRVCTL:

$ srvctl modify service -db crm -service xa_01.example.com -dtp TRUE

Service Thresholds and Alerts

Service-level thresholds enable you to compare achieved service levels against accepted minimum required levels. This provides accountability for 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 an Oracle RAC database.

Service Thresholds and Alerts

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.

Services and Thresholds Alerts: Example

To check the thresholds for the servall service, use the AWR report. You should record output from the report over several successive intervals during which time the system is running optimally. For example, assume that for an email server, the AWR report runs each Monday during the peak usage times of 10:00 AM to 2:00 PM. The AWR report would contain the response time, or DB time, and the CPU consumption time, or CPU time, for calls for each service. The AWR report would also provide a breakdown of the work done and the wait times that are contributing to the response times.

Using DBMS_SERVER_ALERT, set a warning threshold for the payroll service at 0.5 seconds and a critical threshold for the payroll service at 0.75 seconds. You must set these thresholds at all instances within an Oracle RAC database. You can schedule actions using Enterprise Manager jobs for alerts, or you can schedule actions to occur programmatically when the alert is received. In this example, thresholds are added for the servall service and set as shown below:

                  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 => 'payroll');

Verify the threshold configuration by using the following SELECT statement:

                  SELECT metrics_name, instance_name, warning_value, critical_value, observation_period FROM dba_thresholds;

Service Aggregation and Tracing

By default, important statistics and wait events are collected for the work attributed to every service. An application can further qualify a service by MODULE and ACTION names to identify the important transactions within the service. This enables you to locate exactly the poorly performing transactions for categorized workloads. This is especially important when monitoring performance in systems by using connection pools or transaction processing monitors. For these systems, the sessions are shared, which makes accountability difficult.

SERVICE_NAME, MODULE, and ACTION are actual columns in V$SESSION. SERVICE_NAME is set automatically at login time for the user. MODULE and ACTION names are set by the application by using the DBMS_APPLICATION_INFO PL/SQL package or special OCI calls. MODULE should be set to a userrecognizable name for the program that is currently executing. Likewise, ACTION should be set to a specific action or task that a user is performing within a module (for example, entering a new customer).

Another aspect of this workload aggregation is tracing by service. The traditional method of tracing each session produces trace files with SQL commands that can span workloads. This results in a hit-or-miss approach to diagnose problematic SQL. With the criteria that you provide (SERVICE_NAME, MODULE, or ACTION), specific trace information is captured in a set of trace files and combined into a single output trace file. This enables you to produce trace files that contain SQL that is relevant to a specific workload being done.

Service Aggregation Configuration

On each instance, important statistics and wait events are automatically aggregated and collected by service. You do not have to do anything to set this up, except connect with different connect strings by using the services that you want to connect to. However, to achieve a finer level of granularity of statistics collection for services, you must use the SERV_MOD_ACT_STAT_ENABLE procedure in the DBMS_MONITOR package. This procedure enables statistics gathering for additional hierarchical combinations of SERVICE_NAME/MODULE and SERVICE_NAME/MODULE/ACTION. The SERV_MOD_ACT_STAT_DISABLE procedure stops the statistics gathering that was turned on.

The enabling and disabling of statistics aggregation within the service applies to every instance accessing the database. These settings are persistent across instance restarts.

The SERV_MOD_ACT_TRACE_ENABLE procedure enables tracing for services with three hierarchical possibilities: SERVICE_NAME, SERVICE_NAME/MODULE, and SERVICE_NAME/MODULE/ACTION. The default is to trace for all instances that access the database. A parameter is provided that restricts tracing to specified instances where poor performance is known to exist. This procedure also gives you the option of capturing relevant waits and bind variable values in the generated trace files. SERV_MOD_ACT_TRACE_DISABLE disables the tracing at all enabled instances for a given combination of service,module, and action. Like the statistics gathering mentioned previously, service tracing persists across instance restarts.

Service, Module, and Action Monitoring

You can enable performance data tracing for important modules and actions within each service. The performance statistics are available in the V$SERV_MOD_ACT_STATS view. Consider the following actions, as shown below:

– For the ERP service, enable monitoring for the exceptions pay action in the payroll module:

EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( service_name => 'ERP', module_name=> 'PAYROLL',  action_name => 'EXCEPTIONS PAY')

– Under the ERP service, enable monitoring for all the actions in the payroll module:

EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name =>  'ERP', module_name=> 'PAYROLL', action_name => NULL);

– Under the HOT_BATCH service, enable monitoring for all actions in the posting module:

EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name =>  'HOT_BATCH', module_name =>'POSTING', action_name => NULL);

Verify the enabled service, module, action configuration with the following SELECT statement:

COLUMN AGGREGATION_TYPE FORMAT A21 TRUNCATED HEADING 'AGGREGATION' COLUMN PRIMARY_ID FORMAT A20 TRUNCATED HEADING 'SERVICE' COLUMN QUALIFIER_ID1 FORMAT A20 TRUNCATED HEADING 'MODULE' COLUMN QUALIFIER_ID2 FORMAT A20 TRUNCATED HEADING 'ACTION' SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

The output might appear as follows:

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

The following sample SQL*Plus script provides service quality statistics for a five-second interval. You can use these service quality statistics to monitor the quality of a service, to direct work, and to balance services across Oracle RAC instances:

                  SET PAGESIZE 60 COLSEP '|' NUMWIDTH 8 LINESIZE 132 VERIFY OFF FEEDBACK OFF COLUMN service_name FORMAT A20 TRUNCATED HEADING 'Service' COLUMN begin_time HEADING 'Begin Time' FORMAT A10 COLUMN end_time HEADING 'End Time' FORMAT A10 COLUMN instance_name HEADING 'Instance' FORMAT A10 COLUMN service_time HEADING 'Service Time|mSec/Call' FORMAT 999999999 COLUMN throughput HEADING 'Calls/sec'FORMAT 99.99 BREAK ON service_name SKIP 1 SELECT service_name , TO_CHAR(begin_time, 'HH:MI:SS') begin_time , TO_CHAR(end_time,  'HH:MI:SS') end_time , instance_name , elapsedpercall service_time , callspersec throughput FROM gv$instance i , gv$active_services s , gv$servicemetric m WHERE s.inst_id = m.inst_id AND s.name_hash =  m.service_name_hash AND i.inst_id = m.inst_id AND m.group_id = 10 ORDER  BY service_name , i.inst_id , begin_time ;   |                  |          |                 |Service Time     | Service             |Begin Time|End Time  |Instance Sec/Call|Calls/sec --------------------|----------|----------|-----------------|--------- BATCH.cluster01.exam|06:52:38  |06:52:43  |orcl_1  | 117      | .90  ERP.cluster01.exampl|06:52:38  |06:52:43  |orcl_1  | 21       | 2.00                     |07:16:39  |07:16:44  |orcl_3  | 6        | .90  SYS$BACKGROUND      |06:52:38  |06:52:43  |orcl_1  | 655      | .80                     |07:16:39  |07:16:44  |orcl_3  | 782      | .80  SYS$USERS           |06:52:38  |06:52:43  |orcl_1  | 420      | 2.20                     |07:16:39  |07:16:44  |orcl_3  | 761      | .60  orcl.cluster01.examp|06:52:38  |06:52:43  |orcl_1  | 0        | .00                     |07:16:39  |07:16:44  |orcl_3  | 0        | .00  orclXDB             |06:52:38  |06:52:43  |orcl_1  | 0        | .00                     |07:16:39  |07:16:44  |orcl_3  | 0        | .00

Service Performance Views

The service, module, and action information are visible in GV$SESSION and GV$ACTIVE_SESSION_HISTORY. The call times and performance statistics are visible in GV$SERVICE_STATS, GV$SERVICE_EVENT, VG$SERVICE_WAIT_CLASS, V$SERVICEMETRIC, and GV$SERVICEMETRIC_HISTORY. When statistics collection for specific modules and actions is enabled, performance measures are visible at each instance in GV$SERV_MOD_ACT_STATS.

More than 600 performance-related statistics are tracked and visible in GV$SYSSTAT. Of these, 28 statistics are tracked for services. To see the statistics measured for services, run the following query:

SQL> SELECT DISTINCT stat_name FROM v$service_stats;

Of the 28 statistics, DB time and DB CPU are worth mentioning. DB time is a statistic that measures the average response time per call. It represents the actual wall clock time for a call to complete. DB CPU is an average of the actual CPU time spent per call. The difference between response time and CPU time is the wait time for the service. After the wait time is known, and if it consumes a large percentage of response time, then you can trace at the action level to identify the waits

Note: DBA_ENABLED_AGGREGATIONS displays information about enabled on-demand statistic aggregation. DBA_ENABLED_TRACES displays information about enabled traces.