Database Reference
In-Depth Information
be used to enable SQL trace or client statistics for a certain combination of instance service
name, module, and action. In the call to the packaged procedure
DBMS_MONITOR.SERV_MOD_ACT_
TRACE_ENABLE
, merely the instance service name is mandatory. If the module is unspecified,
then the setting affects all modules. Likewise, when service name and module are specified, the
setting affects all actions. Tracing and statistics collection for a certain client identifier are also
possible. Here are some examples which first enable and then disable trace and statistics
collection at various levels:
SQL> EXEC dbms_monitor.serv_mod_act_trace_enable('TEN.oradbpro.com', 'mod', 'act')
SQL> EXEC dbms_monitor.client_id_trace_enable('Ray.Deevers')
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com', 'mod', 'act')
SQL> EXEC dbms_monitor.client_id_stat_enable('Ray.Deevers')
SQL> EXEC dbms_monitor.client_id_stat_disable('Ray.Deevers')
SQL> EXEC dbms_monitor.serv_mod_act_stat_disable('TEN.oradbpro.com', 'mod', 'act')
SQL> EXEC dbms_monitor.client_id_trace_disable('Ray.Deevers')
SQL> EXEC dbms_monitor.serv_mod_act_trace_disable('TEN.oradbpro.com', 'mod', 'act')
Arguments to
DBMS_MONITOR
are case sensitive. The spelling of the service name must match
the value of the column
DBA_SERVICES.NETWORK_NAME
. The settings are persistent in the sense
that they remain in effect across instance restarts. The dictionary views
DBA_ENABLED_TRACES
and
DBA_ENABLED_AGGREGATIONS
reflect the current settings. If we wanted to trace all the sessions
that use the instance service “TEN.oradbpro.com” and enable statistics collection for that
same service and the module “mod”, we could achieve this with the following code:
SQL> SELECT network_name FROM dba_services WHERE network_name LIKE 'TEN.%';
NETWORK_NAME
----------------
TEN.oradbpro.com
SQL> EXEC dbms_monitor.serv_mod_act_trace_enable(service_name=>'TEN.oradbpro.com')
SQL> SELECT trace_type, primary_id, waits, binds FROM dba_enabled_traces;
TRACE_TYPE PRIMARY_ID WAITS BINDS
---------- ---------------- ----- -----
SERVICE TEN.oradbpro.com TRUE FALSE
SQL> EXEC dbms_monitor.serv_mod_act_stat_enable('TEN.oradbpro.com', 'mod')
SQL> SELECT aggregation_type, primary_id, qualifier_id1, qualifier_id2
FROM dba_enabled_aggregations;
AGGREGATION_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2
---------------- ---------------- ------------- -------------
SERVICE_MODULE TEN.oradbpro.com mod
Statistics for service name, module, and action may be retrieved from the dynamic
performance view
V$SERV_MOD_ACT_STATS
, whereas statistics for a client identifier are in
V$CLIENT_STATS
.
The assignment of a database session to a consumer group based on instrumentation
settings is a new feature of the Oracle10
g
database resource manager. Among others, assign-
ments can be made for combinations of service name, module, and action or combinations of
module and action, where merely the first component is mandatory. The settings in effect are
available by querying the view
DBA_RSRC_GROUP_MAPPINGS
. This feature may be used to prioritize
modules and services. The resource manager is shipped with a default plan called SYSTEM_PLAN.
