Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 518

Once monitoring has been enabled, it remains active until such time it is disabled using the following procedure:
EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE (null,null);
These definitions can be verified by querying the DBA_ENABLED_AGGREGATIONS table:
SELECT AGGREGATION_TYPE,
QUALIFIER_ID1 MODULE,
QUALIFIER_ID2 ACTION
FROM DBA_ENABLED_AGGREGATIONS;
AGGREGATION_TYPE MODULE ACTION
--------------------- ---------- ---------
SERVICE_MODULE_ACTION ORDERS Mixed
SERVICE_MODULE_ACTION ORDERS Multiple
SERVICE_MODULE_ACTION ORDERS Read
SERVICE_MODULE_ACTION ORDERS Update
Before monitoring the performance statistics, the application connecting to the database should connect to
the SERVICE_NAME being monitored, and the application should have the module identified in the code. The module
name can be set in the application using the following procedure:
DBMS_APPLICATION_INFO.SET_MODULE (<MODULE NAME>, <ACTION TYPE>);
For example, to let the database know which module is being monitored, the following procedure should be
executed from inside the application module:
EXEC DBMS_APPLICATION_INFO.SET_MODULE ('ORDERS');
Apart from monitoring individual modules, performance-related statistics could also be collected for any specific
action. For example, the performance of various users executing update statements can also be monitored executing
the following procedure:
EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('SRV1', 'ORDERS', 'UPDATE');
Similarly, inside the application ORDERS module, the specific action ( 'UPDATE' ) being modified should also be
identified using the following procedure:
EXEC DBMS_APPLICATION_INFO.SET_MODULE ('ORDERS', 'UPDATE');
Once the statistics collection has been enabled on the database server and on the client side, the performance
metrics can be collected or monitored. For example, the output from the following script against the
GV$SERVICE_STATS view provides a high level indication that DB Time for SRV1 on instance 2 is significantly high:
COL STAT_NAME FORMAT A35
COL MODULE FORMAT A10
COL SERVICE FORMAT A15
COL INST FORMAT 999
SELECT INST_ID INST,
SERVICE_NAME SERVICE,
STAT_NAME,
VALUE