Managing application workloads with Database Services

managing application workloads with Database Services

This section contains:

• About Database Services

• Creating Database Services

• Database Service Data Dictionary views

About Database Services

Database Services (services) is logical abstractions for managing workloads in Oracle Database. Services divide workloads into mutually disjoint groupings. Each service represents a workload with common attributes, service-level thresholds, and priorities. The grouping is based in attributes of work that might include the application function to being used, the priority of Execut Ion for the application function, the job class to being managed, or the data range used in the application function or job C Lass. For example, the Oracle e-business Suite defines a service for each responsibility, such as General Ledger, accounts recei Vable, order entry, and so on. When you configure database services, you give each service a unique global name, associated performance goals, and ASSOCI ated importance. The services is tightly integrated with Oracle Database and is 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 service as part of the Conne Ction in TNS connect data. If no service name is included and the Net Services file Listener.ora designates a default service, the connection uses th E default service.

Services enable Configure a workload, administer it, enable and disable it, and measure the workload as a single en Tity. You can do this using standard tools such as the Database Configuration Assistant (DBCA), net configuration Assistant (net CA), and Oracle Enterprise Manager. Enterprise Manager supports viewing and operating services as a whole, with drill down to the instance-level when needed.

In the Oracle Real application Clusters (Oracle RAC) environment, a service can span one or more instances and Facilitat E workload balancing based on transaction performance. This provides end-to-end unattended recovery, rolling changes by workload, and full location transparency. Oracle RAC also enables manage several service features with Enterprise Manager, the DBCA, and the Server Control u Tility (srvctl ).

Services also offer a 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 is anon Ymous and GKFX. With services, workloads is visible and measurable. Resource consumption and waits is attributable by application. Additionally, resources assigned to services can is augmented when loads increase or decrease. This dynamic resource allocation enables a cost-effective solution for meeting demands as they occur. For example, services was measured automatically and the performance is compared to service-level thresholds. Performance violations is reported to Enterprise Manager, enabling the execution of automatic or scheduled solutions.

Several Oracle Database features support services. The Automatic Workload Repository (AWR) manages the performance of services. AWR Records service performance, including execution times, wait classes, and resources consumed by service. AWR alerts warn when service response time thresholds is exceeded. The dynamic Views report current service performance metrics with one hour of history. Each service has a quality-of-service thresholds for response time and CPU consumption.

In addition, the Database Resource Manager can map services to consumer groups. Therefore, you can automatically manage the one service relative to others. You can use the consumer groups to define relative precedence in terms of either ratios or resource consumption. For more information, see Chapter, "Managing Resources with Oracle Database Resource Manager," and specifically in "Spe Cifying session-to–consumer Group Mapping Rules ".

You also can specify an edition attribute for a service. Editions make it possible to has a versions of the same objects in the database. When you specify an edition attribute for a service, all subsequent connections that specify the 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 service attribute can make it easier to manage resource usage. For example, services associated with the edition can be placed on a separate instance in an Oracle RAC environment, and th E Database Resource Manager can manage resources used by different editions by associating Resource plans with the Corresp Onding Services.

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

You can also define Preconnect application services in Oracle RAC databases. Preconnect services span instances to support a service in the event of a failure. The Preconnect service supports TAF preconnect mode and is managed transparently when using Oracle RAC.

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

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

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

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

Creating Database Services

There is a few ways to the CREATE database services, depending on your database configuration.

To create a database service:

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

  Add - -s Service_Name

• If your single-instance database is not being managed by Oracle Restart, do one of the following:

  • Append the desired service name to the SERVICE_NAMES parameter.

  • Call the package DBMS_SERVICE.CREATE_SERVICE procedure.

• (Optional) Define service attributes with Oracle Enterprise Manager or with DBMS_SERVICE.MODIFY_SERVICE .

Database Service Data Dictionary views

You can find service information in the following service-specific views:

• DBA_SERVICES

• ALL_SERVICESOrV$SERVICES

• V$ACTIVE_SERVICES

• V$SERVICE_STATS

• V$SERVICE_EVENT

• V$SERVICE_WAIT_CLASSES

• V$SERV_MOD_ACT_STATS

• V$SERVICE_METRICS

• V$SERVICE_METRICS_HISTORY

The following additional views also contain some information about services:

• V$SESSION

• V$ACTIVE_SESSION_HISTORY

• DBA_RSRC_GROUP_MAPPINGS

• DBA_SCHEDULER_JOB_CLASSES

• DBA_THRESHOLDS

Managing application workloads with Database Services