Services add a new dimension for performance tuning. With services, workloads are visible and measurable, and therefore resource consumption and wait times are attributable by application. Tuning by using "service and SQL" replaces tuning by "session and SQL" in the majority of systems where all sessions are anonymous and shared.
The AWR maintains performance statistics that include information about response time, throughput, resource consumption, and wait events for all services and work that a database performs. Oracle Database also maintains metrics, statistics, wait events, wait classes, and SQL-level traces for services. You can optionally augment these statistics by defining modules within your application to monitor certain statistics. You can also define the actions within those modules that business critical transactions should execute in response to particular statistical values.
Enable module and action monitoring using the
DBMS_MONITOR PL/SQL package. For example, for connections that use the
erp service, the following command enables monitoring for the
exceptions pay action in the
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'ERP', MODULE_NAME=> 'PAYROLL', ACTION_NAME => 'EXCEPTIONS PAY');
For connections that use the
erp service, the following command enables monitoring for all actions in the
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(SERVICE_NAME => 'ERP', MODULE_NAME=> 'PAYROLL', ACTION_NAME => NULL);
DBA_ENABLED_AGGREGATIONS view to verify that you have enabled monitoring for application modules and actions.
Statistics aggregation and tracing by service are global in scope for Oracle RAC databases. In addition, these statistic aggregations are persistent across instance restarts and service relocations for both Oracle RAC and noncluster Oracle databases.
The service, module, and action names are visible in
V$SQL views. The call times and performance statistics are visible in
V$SERVICEMETRIC_HISTORY. When you enable statistics collection for an important transaction, you can see the call speed for each service, module, and action name at each database instance using the
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 ;