Oracle Best Practices Part 4

In this series during Part 1 I mentioned below best practices;
1- Put your Database into ARCHIVELOG Mode
2- Comment on Tables and Columns similar to your PL/SQL or SQL Source Codes
3- Use DBMS_STATS for statistic collection
4- Using Recovery Manager(RMAN) for Backup and Recovery needs

following Part 2 I mentioned below best practices;
5- Resumable Statements and Space Allocation
6- Maintenance of Global Partitioned Indexes
7- Default Tablespaces
8- PL/SQL Bulk Operations
9- Locking Issues
10- Oracle System Event Triggers
11- Autonomous Transactions

and following Part 3 I mentioned below best practice;
12- Learn what is already provided with PL/SQL language

13- Defining Application Services for Oracle Database 10g

Services are logical abstractions for managing workloads in Oracle Database 10g, they represent groups of applications with common attributes, service level thresholds, and priorities. Application functions can be divided into workloads identified by services. For example, the CRM application can define a service for each responsibility. Or a HR application can be a specific service. A service can span one or more instances of an Oracle database or multiple databases in a global cluster, and a single instance can support multiple services.

Server side work, such as the Scheduler, parallel query, and Oracle Streams Advanced Queuing set the service name as part of the workload definition. For the Scheduler, jobs are assigned to job classes, and job classes run within services. For parallel query and parallel DML, the query coordinator connects to a service, and the parallel query slaves inherit the service for the duration of the query. For Oracle Streams Advanced Queuing, streams queues are accessed using services. Work running under a service inherits the thresholds and attributes for the service and is measured as part of the service.

The Database Resource Manager binds services to consumer groups and priorities. This lets services be managed in the database in the order of their importance. For example, you can define separate services for high priority online users and lower priority internal reporting applications. Likewise, you can define gold, silver, and bronze services to prioritize the order in which requests are serviced for the same application.

The Automatic Workload Repository lets you analyze the performance of workloads using the aggregation dimension for service. The Automatic Workload Repository automatically maintains response time and CPU consumption metrics, performance and resource statistics wait events, threshold-based alerts, and performance indexes for all services.

Service, module, and action tags identify operations within a service at the server. (MODULE and ACTION are set by the application) End to end monitoring enables aggregation and tracing at service, module, and action levels to identify high load operations. Oracle Enterprise Manager administers the service quality thresholds for response time and CPU consumption, monitors the top services, and provides drill down to the top modules and top actions for each service.

With the Automatic Workload Repository, performance management by the service aggregation makes sense when monitoring by sessions may not. For example, in systems using connection pools or transaction processing monitors, the sessions are shared, making accountability difficult.

With Real Application Clusters(RAC), services can be provisioned on different instances based on their current performance. Connect time routing and runtime routing algorithms balance the workload across the instances offering a service. The metrics for server-side connection load balancing are extended to include service performance. Connections are shared across instances according to the current service performance. Using service performance for load balancing accommodates nodes of different sizes and workloads with competing priorities. It also prevents sending work to nodes that are hung or failed.

The Automatic Workload Repository maintains metrics for service performance continuously. These metrics are available when routing runtime requests from mid-tier servers and TP monitors to RAC. For example, Oracle JDBC connection pools use the service data when routing the runtime requests to instances offering a service.
High Availability with Services

RAC use services to enable uninterrupted database operations. Services are tightly integrated with the Oracle Clusterware high availability framework that supports RAC. For planned outages, RAC provides interfaces to relocate, disable, and enable services. When the service at an instance stops, the event is used to interrupt applications using the service at that instance. Using the notification eliminates the client waiting on TCP timeouts. The events are integrated with Oracle JDBC connection pools and Transparent Application Failover (TAF).

With Oracle Data Guard, production services are offered at the production site. Other standby sites can offer reporting services when operating in read only mode.

This section describes Oracle Database 10g services and includes the following topics:
* Deploying Services
* Configuring Services
* Using Services

Code listing 76 : Oracle Services Example

Continue reading with Part 5

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used :
Oracle® Database Administrator’s Guide 10g Release 2 (10.2) – Chapter 2 Creating an Oracle Database – Defining Application Services for Oracle Database 10g
Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)-Chapter 95 DBMS_SERVICE
Services for a DBA presentation by David Austin
DBMS_SERVICE and net service names by Mr.Lutz Hartmann


  1. Hey *great* post… defining services on 10g – for ALL databases, not just RAC, is something I think is overlooked WAY too often. Thought I’d add one more reference to the list too; it’s a bit long and deep but it’s thorough.

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s