2.8 Managing Application Workloads with Database Services

«« Previous
Next »»

A database service is a named representation of one or more database instances. Services enable you to group database workloads and route a particular work request to an appropriate instance.
  • Database Services
A database service represents a single database. This database can be a single-instance database or an Oracle Real Application Clusters (Oracle RAC) database with multiple concurrent database instances. A global database service is a service provided by multiple databases synchronized through data replication.


1. About Database Services

Database services divide workloads for a single database into mutually disjoint groupings.

Each database service represents a workload with common attributes, service-level thresholds, and priorities. The grouping is based on attributes of work that might include the application function to be used, the priority of execution for the application function, the job class to be managed, or the data range used in the application function or job class. For example, the Oracle E-Business Suite defines a database service for each responsibility, such as general ledger, accounts receivable, order entry, and so on. When you configure database services, you give each service a unique name, associated performance goals, and associated importance. The database services are tightly integrated with Oracle Database and are maintained in the data dictionary.

Connection requests can include a database service name. Thus, middle-tier applications and client/server applications use a service by specifying the database service as part of the connection in TNS connect data. If no database service name is included and the Net Services file listener.ora designates a default database service, then the connection uses the default database service.

Database services enable you to configure a workload for a single database, administer it, enable and disable it, and measure the workload as a single entity. You can do this using standard tools such as the Database Configuration Assistant (DBCA), Oracle Net Configuration Assistant, and Oracle Enterprise Manager Cloud Control (Cloud Control). Cloud Control supports viewing and operating services as a whole, with drill down to the instance-level when needed.

In an Oracle Real Application Clusters (Oracle RAC) environment, a database service can span one or more instances and facilitate workload balancing based on transaction performance. This capability provides end-to-end unattended recovery, rolling changes by workload, and full location transparency. Oracle RAC also enables you to manage several database service features with Cloud Control, the DBCA, and the Server Control utility (SRVCTL).

Database services describe applications, application functions, and data ranges as either functional services or data-dependent services. Functional services are the most common mapping of workloads. Sessions using a particular function are grouped together. In contrast, data-dependent routing routes sessions to database services based on data keys. The mapping of work requests to database services occurs in the object relational mapping layer for application servers and TP monitors. For example, in Oracle RAC, these ranges can be completely dynamic and based on demand because the database is shared.

In addition to database services that are used by applications, Oracle Database also supports two internal database services: SYS$BACKGROUND is used by the background processes only, and SYS$USERS is the default database service for user sessions that are not associated with services.

Using database services requires no changes to your application code. Client-side work can connect to a named database service. Server-side work, such as Oracle Scheduler, parallel execution, and Oracle Database Advanced Queuing, set the database service name as part of the workload definition. Work requests executing under a database service inherit the performance thresholds for the service and are measured as part of the service.

2. Database Services and Performance

Database services offer an extra dimension in performance tuning.

Tuning by "service and SQL" can replace tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared. With database services, workloads are visible and measurable. Resource consumption and waits are attributable by application. Additionally, resources assigned to database services can be augmented when loads increase or decrease. This dynamic resource allocation enables a cost-effective solution for meeting demands as they occur. For example, database services are measured automatically, and the performance is compared to service-level thresholds. Performance violations are reported to Cloud Control, enabling the execution of automatic or scheduled solutions.

3. Oracle Database Features That Use Database Services

Several Oracle Database features support database services.

The Automatic Workload Repository (AWR) manages the performance of services. AWR records database service performance, including execution times, wait classes, and resources consumed by services. AWR alerts warn when database service response time thresholds are exceeded. The dynamic views report current service performance metrics with one hour of history. Each database service has quality-of-service thresholds for response time and CPU consumption.

In addition, the Database Resource Manager can map database services to consumer groups. Therefore, you can automatically manage the priority of one database service relative to others. You can use consumer groups to define relative priority in terms of either ratios or resource consumption. This is described in more detail in Managing Resources with Oracle Database Resource Manager, and specifically in "Specifying Session-to–Consumer Group Mapping Rules".

You also can specify an edition attribute for a database service. Editions make it possible to have two or more versions of the same objects in the database. When you specify an edition attribute for a database service, all subsequent connections that specify the database service use this edition as the initial session edition. This is described in more detail in "Setting the Edition Attribute of a Database Service".

Specifying an edition as a database service attribute can make it easier to manage resource usage. For example, database services associated with an edition can be placed on a separate instance in an Oracle RAC environment, and the Database Resource Manager can manage resources used by different editions by associating resource plans with the corresponding database services.

For Oracle Scheduler, you optionally assign a database service when you create a job class. During execution, jobs are assigned to job classes, and job classes can run within database services. Using database services with job classes ensures that the work executed by the job scheduler is identified for workload management and performance tuning.

For parallel query and parallel DML, the query coordinator connects to a database service just like any other client. The parallel query processes inherit the database service for the duration of the execution. At the end of query execution, the parallel execution processes revert to the default database service.

4. Creating Database Services

There are a few ways to create database services, depending on your database configuration.

Note: This section describes creating services locally. You can also create services to operate globally

To create a database service:

1. If your single-instance database is being managed by Oracle Restart, use the SRVCTL utility to create the database service.

srvctl add service -db db_unique_name -service service_name

2. If your single-instance database is not being managed by Oracle Restart, do one of the following:
     - Append the desired database service name to the SERVICE_NAMES parameter.
     - Call the DBMS_SERVICE.CREATE_SERVICE package procedure.
3. (Optional) Define database service attributes with Cloud Control or with DBMS_SERVICE.MODIFY_SERVICE.

Oracle Net Listener (the listener) receives incoming client connection requests and manages the traffic of these requests to the database server. The listener handles connections for registered services, and it supports dynamic service registration.
  • Global Data Services
Starting with Oracle Database 12c, you can use Global Data Services (GDS) for workload management involving multiple Oracle databases. GDS enables administrators to automatically and transparently manage client workloads across replicated databases that offer common services. These common services are known as global services.

GDS enables you to integrate multiple databases in various locations into private GDS configurations that can be shared by global clients. Benefits include the following:

1. Enables central management of global resources
2. Provides global scalability, availability, and run-time load balancing
3. Allows you to dynamically add databases to the GDS configuration and dynamically migrate global services
4. Extends service management, load balancing, and failover capabilities for distributed environments of replicated databases that use features such as Oracle Active Data Guard, Oracle GoldenGate, and so on
5. Provides high availability through seamless failover of global services across databases (located both locally or globally)
6. Provides workload balancing both within and between data centers through services, connection load balancing, and runtime load balancing
7. Allows efficient utilization of the resources of the GDS configuration to service client requests
  • Database Service Data Dictionary Views
You can query data dictionary views to find information about database services.

You can find information about database services in the following views:
  1. DBA_SERVICES
  2. ALL_SERVICES or V$SERVICES
  3. V$ACTIVE_SERVICES
  4. V$SERVICE_STATS
  5. V$SERVICE_EVENT
  6. V$SERVICE_WAIT_CLASSES
  7. V$SERV_MOD_ACT_STATS
  8. V$SERVICE_METRICS
  9. V$SERVICE_METRICS_HISTORY
The following additional views also contain some information about database services:
  1. V$SESSION
  2. V$ACTIVE_SESSION_HISTORY
  3. DBA_RSRC_GROUP_MAPPINGS
  4. DBA_SCHEDULER_JOB_CLASSES
  5. DBA_THRESHOLDS
The ALL_SERVICES view includes a GLOBAL_SERVICE column, and the V$SERVICES and V$ACTIVE_SERVICES views contain a GLOBAL column. These views and columns enable you to determine whether a database service is a global service.

«« Previous
Next »»