Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 189

For example, to enable statistics collection for module Process Orders in service TAPS, the following should be
executed on the database server on any one of the available instances:
SQL> EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('TAPS','Process Orders');
Once monitoring has been enabled, it remains active until such time as 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.
Script: MVRACPDnTap_enableAgg.sql
SELECT AGGREGATION_TYPE,
QUALIFIER_ID1 MODULE,
QUALIFIER_ID2 ACTION
FROM DBA_ENABLED_AGGREGATIONS;
AGGREGATION_TYPE MODULE ACTION
-------------------- -------------------- ------
SERVICE_MODULE Process Orders
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 ('Process Orders');
Apart from monitoring individual modules, performance-related statistics can also be collected for any specific
action. For example, to monitor the performance of various users executing update statements, you can execute the
following procedure:
SQL> EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('TAPS','Process Orders','UPDATE');
This feature of collecting a performance matrix for an action type within a module was not available until Oracle
Database 10g and is a great feature that can be easily used. In a RAC environment where workload is distributed
across multiple instances in the cluster, this helps collect statistics for a given module across the cluster.
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 lowercase “time” to match the other two uses? for TAPS on instance 1 is
significantly high.