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 |