DBMS_MONITOR.SERV_MOD_ACT_TRACE

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE Procedure

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:

NEVER
FIRST EXECUTION (default)
ALL_EXECUTIONS

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.