Automatic Work Load Management and High Availability

articles: 

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