Automatic Work Load Management and High Availability
Automatic Work Load Management
Application workloads can be defined as services so that they can be individually managed and controlled.
We can control and monitor which processing resources are allocated to each service during both normal operations and in response to failures.
Performance metrics are tracked by service and thresholds set to automatically generate alerts should these thresholds be crossed.
CPU resource allocations and resource consumption controls are managed for services using Resource Manager.
we deifne rules to automatically allocate processing resources to services. Oracle RAC 10g instances can be allocated to process individual services or multiple services as needed.
These allocation rules can be modified dynamically to meet changing business needs. T
These rules could be modified at a desire point in time to ensure that there are enough processing resources to complete critical running business functions on time.
Rules can also be defined so that when instances running critical services fail, the workload will be automatically shifted to instances running less critical workloads.
Defining Services
we can define a service for each application component, such as HR, Finance, ERP, and so on.
Middle tier applications and clients select a service by specifying the service name in the TNS connect data when connecting to the database.
The important point here is , no changes need be made to the applications themselves.
For example, using Net Easy*Connection in 10g you would login using: scott/tiger@//myVIP/myservice;
We use DBCA or SRVCTL to initially define services and assign them to a set of PREFERRED instances that the service will run on at startup and to set of AVAILABLE instances that will be automatically used should failures occur.
These definitions are used by the Listener to assign connections to instances when new sessions are established.
After failure, CRS (Cluster Ready Services) automatically tries to restart services in 10g.
If you do not wish a service to be restarted, you should “disable” the service. This might be useful for planned maintenance.
Quick Demo on Automatic Work Load Performance Tracking using DBMS_MONITOR.
We can enable statistics aggregation to monitor performance problems based on a client identifier, or for a given hierarchical set of service, module, and action names.
This initiates aggregation of key metrics and statistics that are useful for problem diagnosis and analysis.
The DBMS_MONITOR PL/SQL package can be used to control additional tracing and statistics gathering. The DBMS_MONITOR package contains the following procedures used to enable and disable additional statistics aggregation:
CLIENT_ID_STAT_ENABLE: Enable statistics accumulation for a given client identifier
CLIENT_ID_STAT_DISABLE: Disable statistics accumulation for a given client identifier
SERV_MOD_ACT_STAT_ENABLE: Enable statistics accumulation for a given hierarchy of service name, module name, and action name
SERV_MOD_ACT_STAT_DISABLE: Disable statistics accumulation for a given hierarchy of service name, module name, and action name
We would be discussing on SERV_MOD_ACT_STAT_ENABLE and SERV_MOD_ACT_STAT_DISBLE.
SESSION 1
CONN SCOTT/TIGER
SQL>DROP TABLE TEST;
SQL>CREATE TABLE test (x number);
Table created.
CREATE OR REPLACE PROCEDURE WORKLOAD_DEMO IS
BEGIN
dbms_session.set_identifier('TEST:WL');
dbms_application_info.set_module('WORKLOAD_DEMO', 'test_demo');
FOR i in 1 .. 10000 loop
insert into test values (i);
end loop;
end WORKLOAD_DEMO;
/
Procedure created.
The above procedure associates a client_identifier named TEST:WL , a module named WORKLOAD_DEMO and the action as test_demo
MODULE and ACTION name are a way of tying a portion of the application code to the database work done on its behalf.
The MODULE name is set to a user recognizable name for the program that is currently executing (script or form).
The ACTION name is set to a specific action that a user is performing within a module (’SELECTS’, or ’INSERTS’ to be more specific we could say viewing the sales report or Creating a ORDER Journey).
In our case the module is nothing but our Procedure and action is execution of our procedure.
SQL> EXEC WORKLOAD_DEMO;
PL/SQL procedure successfully completed.
SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;
SID
----------
139
SESSION 2
CONN / AS SYSDBA
SQL> set pagesize 20
SQL> set linesize 140
SQL> col client_identifier format a30
SQL> col service_name format a30
SQL> col module format a20
SQL> col action format a20
SQL> SELECT sid, client_identifier, service_name, module, action
2 FROM gv$session;
SID CLIENT_IDENTIFIER SERVICE_NAME MODULE ACTION
---------- ------------------------------ ------------------------------ ------- ---------------
139 TEST:WL SYS$USERS WORKLOAD_DEMO test_demo
142 SYS$BACKGROUND
148 SYS$BACKGROUND
151 SYS$BACKGROUND
154 SYS$BACKGROUND
155 SYS$BACKGROUND
158 SYS$BACKGROUND
159 SYS$BACKGROUND
160 SYS$BACKGROUND
161 SYS$BACKGROUND
162 SYS$BACKGROUND
163 SYS$BACKGROUND
164 SYS$BACKGROUND
165 SYS$BACKGROUND
166 SYS$BACKGROUND
167 SYS$BACKGROUND
17 rows selected.
This result shows that we have a client_identifier named TEST:WL , a module named WORKLOAD_DEMO and the action as test_demo.
SERVICE_NAME Points to the services currently configured.
Services are a logical abstraction for managing workloads.
For example, we can define a service for each responsibility with in the system, such as HR,PAYROLL and so on.
Each service has the following attributes:
globally unique name – that identifies the service in the local cluster and globally for data guard.
quality of service thresholds for response time and CPU consumption.
priority – relative to other services, defined in terms of either ratio of resource consumption or priority.
In a RAC environment, services have two additional attributes
preferred configuration for High Availability – a description of how to distribute the services when the first system starts.
TAF policy for High Availability – none, basic, or preconnect. – managed automatically using services
In addition to application services, the RDBMS also supports 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 application services.
In our case Since we have not created a service , the default service as provided by oracle SYS$USERS is used.
To configure services in single Oracle instance environments use the DBMS_SERVICE package.
For backward compatibility, services are also created implicitly when the service_names parameter is set for the instance.
Note:To configure the high availability features of services in Oracle 10g RAC environments, use the DBCA and NETCA or the Server Control (SRVCTL) utility.
This definition process creates a number of HA resources that are managed by the clusterware to keep the services available.
Statistics aggregation across general dimensions - for example, services, modules and actions - is frequently more useful than the session-based aggregation that is supported today.
Performance management by the service aggregation makes sense when monitoring by sessions may not.
For example, in systems using connection pools and transaction processing monitors, the sessions are shared, making accountability difficult.
Tune using “service and SQL” rather than “session and SQL”.Service, module, and action tags identify operations at the server.
(MODULE and ACTION name are set by the application for finer grain reporting) The DBMS_MONITOR package enables aggregation and tracing at the service, module, and action levels to identify high load operations.
The service, module, and action tags provide major and minor boundaries to discriminate the work and the processing flow.
This new aggregation level allows tuning groups of SQL that run together (at service, module, and action levels).
Now lets check for any statistics on the above module or any other module.
Since we have not enabbled the statistics collection the below query would return no rows.
SQL> SELECT * FROM v_$serv_mod_act_stats; no rows selected
Let us enable the statistics collection for the module WORKLOAD_DEMO For all the actions performed and we associate this to a service as well.
The service we associate here is SERVICE_DEMO as specified an IN parameter in the dbms_monitor.serv_mod_act_stat_enable.
Note that this service_name will be reflected under v_$serv_mod_act_stats only and not under v$session.
SQL> exec dbms_monitor.serv_mod_act_stat_enable('SERVICE_DEMO', 'WORKLOAD_DEMO', dbms_monitor.ALL_ACTIONS);
PL/SQL procedure successfully completed.
We can now check for statitics for the above module. The values are shown as zero as we dont have a valid service ie the service SERVICE_DEMO which we referenced is not created using the
DBMS_SERVICE or the SERVICE_NAME Parameter.
So it is expected to see a value of zero for all the statistics , But when we provide a valid service for collecting the services in our case the SYS$USERS,we can capture the relevant statistics.
STATISTICS WITH AN INVALID SERVICE
SQL> col service_name format a20 SQL> col stat_name format a30 SQL> SQL> SELECT * FROM v_$serv_mod_act_stats; AGGREGATION_TYPE SERVICE_NAME MODULE ACTION STAT_ID STAT_NAME VALUE --------------------- -------------------- -------------------- ---------- ---------- ---------- ------------------------------ SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2882015696 user calls 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3649082374 DB time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2748282437 DB CPU 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 63887964 parse count (total) 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1431595225 parse time elapsed 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2453370665 execute count 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2821698184 sql execute elapsed time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 85052502 opened cursors cumulative 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3143187968 session logical reads 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 263124246 physical reads 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1190468109 physical writes 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1236385760 redo size 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 582481098 user commits 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3211650785 workarea executions - optimal 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 798730793 workarea executions - onepass 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3804491469 workarea executions - multipass 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3678609077 session cursor cache hits 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3671147913 user rollbacks 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 916801489 db block changes 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2877738702 gc cr blocks received 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1759426133 gc cr block receive time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 326482564 gc current blocks received 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1388758753 gc current block receive time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 2432034337 cluster wait time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3868577743 concurrency wait time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 1099569955 application wait time 0 SERVICE_MODULE SERVICE_DEMO WORKLOAD_DEMO 3332107451 user I/O wait time 0 27 rows selected.
STATISTICS WITH A VALID SERVICE
SQL> exec dbms_monitor.serv_mod_act_stat_enable('SYS$USERS', 'WORKLOAD_DEMO', dbms_monitor.ALL_ACTIONS);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v_$serv_mod_act_stats;
AGGREGATION_TYPE SERVICE_NAME MODULE ACTION STAT_ID STAT_NAME
VALUE
--------------------- -------------------- -------------------- --------- -------- ---------- SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2882015696 user calls 15
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3649082374 DB time 165238505
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2748282437 DB CPU 165233416
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 63887964 parse count (total) 107
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 1431595225 parse time elapsed 21469
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2453370665 execute count 469817
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2821698184 sql execute elapsed time 165216703
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 85052502 opened cursors cumulative 8
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3143187968 session logical reads 962902
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2263124246 physical reads 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 1190468109 physical writes 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 236385760 redo size 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 582481098 user commits 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3211650785 workarea executions - optimal 500
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 798730793 workarea executions - onepass 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3804491469 workarea executions - multipas 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3678609077 session cursor cache hits 104
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3671147913 user rollbacks 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 916801489 db block changes 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2877738702 gc cr blocks received 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 1759426133 gc cr block receive time 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 326482564 gc current blocks received 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 1388758753 gc current block receive time 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 2432034337 cluster wait time 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3868577743 concurrency wait time 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 1099569955 application wait time 0
SERVICE_MODULE SYS$USERS WORKLOAD_DEMO 3332107451 user I/O wait time 0
27 rows selected.
This way if we identify that a particular section of our application is causing the problem and we need a quick statitics on this section of this application,this tool can be very helpfull.
In our case the particular section that we refered was nothing but the procedure WORKLAOD_DEMO.
Once we have collected the statistics we disable the statistics collection.
SQL> exec dbms_monitor.serv_mod_act_stat_disable('SERVICE_DEMO', 'WORKLOAD_DEMO', dbms_monitor.ALL_ACTIONS);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.serv_mod_act_stat_disable('SYS$USERS', 'WORKLOAD_DEMO', dbms_monitor.ALL_ACTIONS);
PL/SQL procedure successfully completed.
SQL>SELECT * FROM v_$serv_mod_act_stats;
no rows selected
VIEWS TO SUPPORT CLIENT BASED AND SERVICE BASED STATISTIC AGGREGATION
DBA_ENABLED_AGGREGATIONS: Information about enabled on-demand statistic aggregation
V$CLIENT_STATS: Measures for all sessions that are active for the client identifier per instance; the statistics displayed are a subset of system statistics for a given client.
V$SERVICE_STATS: Minimal set of performance statistics
V$SERV_MOD_ACT_STATS: The same set of performance statistics as V$SERVICE_STATS for a specific combination of service/module/action names
V$SVCMETRIC: Continuous metrics for elapsed time and CPU use
We have used the V$SERV_MOD_ACT_STATS in our case.
Refer : {ORACLE_HOME}/rdbms/admin/dbmsmntr.sql
For further reading refer : http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html
- sriram717's blog
- Log in to post comments