This procedure, which was introduced in Oracle 10.1, enables event 10046 trace for specific services and also for specific modules and actions.
Argument Name | Type | In/Out | Default? |
SERVICE_NAME | VARCHAR2 | IN | |
MODULE_NAME | VARCHAR2 | IN | DEFAULT |
ACTION_NAME | VARCHAR2 | IN | DEFAULT |
WAITS | BOOLEAN | IN | DEFAULT |
BINDS | BOOLEAN | IN | DEFAULT |
INSTANCE_NAME | BOOLEAN | IN | DEFAULT |
PLAN_STAT | VARCHAR2 | IN | DEFAULT |
The SERVICE_NAME argument is mandatory
The MODULE_NAME can be optionally specified
The ACTION_NAME can also be optionally specified. If the ACTION_NAME is specified then the MODULE_NAME must also be specified
There are two optional Boolean parameters, WAITS and BINDS, both of which default to FALSE.
In a RAC environment, the INSTANCE_NAME argument can be used to speciify the instance on which trace will be enabled.
In Oracle 11.1 and above PLAN_STAT specifies the dump frequency for row source (STAT) statistics. Values can be:
PLAN_STAT is useful when individual statement executions are affected by data cardinality / selectivity
For example to enable event 10046, level 1 trace for service name 'SERVICE1' use:
EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'SERVICE1');
To enable event 10046, level 4 trace (bind variables) for service name 'SERVICE1' use:
EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'SERVICE1',binds=>true);
To enable event 10046, level 8 trace (waits) for CLIENT_ID 'ID1' use:
EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'SERVICE1',waits=>true);
To enable event 10046, level 1 trace for service name 'SERVICE1' and module 'MODULE1' use:
EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'SERVICE1',module_name=>'MODULE1');
To enable event 10046, level 1 trace for service name 'SERVICE1', module 'MODULE1' and action 'ACTION1' use:
EXECUTE dbms_monitor.serv_mod_act_trace_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
The current state of service/module/action trace is reported in the data dictionary view DBA_ENABLED_TRACES. This contains the following columns:
Name | Type |
TRACE_TYPE | VARCHAR2(21) |
PRIMARY_ID | VARCHAR2(64) |
QUALIFIER_ID1 | VARCHAR2(48) |
QUALIFIER_ID2 | VARCHAR2(32) |
WAITS | VARCHAR2(5) |
BINDS | VARCHAR2(5) |
INSTANCE_NAME | VARCHAR2(16) |
For service trace, the TRACE_TYPE column will be 'SERVICE'.
For service/module trace, the TRACE_TYPE column will be 'SERVICE_MODULE'.
For service/module/action trace, the TRACE_TYPE column will be 'SERVICE_MODULE_ACTION'.
The PRIMARY_ID column will contain the service name.
The QUALIFIER_ID1 column will contain the module name.
The QUALIFIER_ID2 column will contain the action name.
The DBA_ENABLED_TRACES view is based on the underlying data dictionary table SYS.WRI$_TRACING_ENABLED.