This procedure, which was introduced in Oracle 10.1, enables statistic collection for specified services and modules and also for specific actions.
| Argument Name | Type | In/Out | Default? |
| SERVICE_NAME | VARCHAR2 | IN | |
| MODULE_NAME | VARCHAR2 | IN | |
| ACTION_NAME | VARCHAR2 | IN | DEFAULT |
The SERVICE_NAME and MODULE_NAME arguments are both mandatory. Note this differs from the functionality of SERV_MOD_ACT_TRACE_ENABLE.
The ACTION_NAME can also be optionally specified.
For example to enable statistics collection for service name 'SERVICE1' and module 'MODULE1' use:
EXECUTE dbms_monitor.serv_mod_act_stat_enable (service_name=>'SERVICE1',module_name=>'MODULE1');
To enable statistics collection for service name 'SERVICE1', module 'MODULE1' and action 'ACTION1' use:
EXECUTE dbms_monitor.serv_mod_act_stat_enable (service_name=>'SERVICE1',module_name=>'MODULE1',action_name=>'ACTION1');
The module name and action name are stored in the SGA for each session. They can be set using the SET_MODULE and SET_ACTION procedures of the DBMS_APPLICATION_INFO package which was introduced in Oracle 8.0.
For example to set the module name to MODULE1 and the action name to ACTION1 use:
EXECUTE DBMS_SESSION.SET_MODULE (module_name=>'MODULE1',action_name=>'ACTION1');
To subsequently set the action name to ACTION2 use:
EXECUTE DBMS_SESSION.SET_ACTION (action_name=>'ACTION2');
The previously specified module name will be retained
Statistics are reported in the V$SERV_MOD_ACT_STATS dynamic performance view. This view has the following columns:
| Name | Type |
| AGGREGATION_TYPE | VARCHAR2(21) |
| SERVICE_NAME | VARCHAR2(64) |
| MODULE_NAME | VARCHAR2(49) |
| ACTION_NAME | VARCHAR2(33) |
| STAT_ID | NUMBER |
| STAT_NAME | VARCHAR2(64) |
| VALUE | NUMBER |
The AGGREGATION_TYPE column will contain the value 'SERVICE_MODULE' or 'SERVICE_MODULE_ACTION'.
This view reports a limited subset of statistics. In Oracle 10.2 these include the following:
| Statistic Name |
| DB_CPU |
| DB time |
| application wait time |
| cluster wait time |
| concurrency wait time |
| db block changes |
| execute count |
| gc cr block receive time |
| gc cr blocks received |
| gc current block receive time |
| gc current blocks received |
| opened cursors cumulative |
| parse count (total) |
| parse time elapsed |
| physical reads |
| physical writes |
| redo size |
| session cache cursor hits |
| session logical reads |
| sql execute elapsed time |
| user I/O wait time |
| user calls |
| user commits |
| user rollbacks |
| workarea executions - multipass |
| workarea executions - onepass |
| workarea executions - optimal |