111 DBMS_MONITOR
The DBMS_MONITOR
package enables you to use PL/SQL for controlling additional tracing and statistics gathering.
The chapter contains the following topics:
111.1 Summary of DBMS_MONITOR Subprograms
This table lists the DBMS_MONITOR
subprograms and briefly describes them.
Table 111-1 DBMS_MONITOR Package Subprograms
Subprogram | Description |
---|---|
Disables statistic gathering previously enabled for a given Client Identifier |
|
Enables statistic gathering for a given Client Identifier |
|
Disables the trace previously enabled for a given Client Identifier globally for the database |
|
Enables the trace for a given Client Identifier globally for the database |
|
Disables SQL trace for the whole database or a specific instance |
|
Enables SQL trace for the whole database or a specific instance |
|
Disables statistic gathering enabled for a given combination of Service Name, |
|
Enables statistic gathering for a given combination of Service Name, |
|
Disables the trace for |
|
Enables SQL tracing for a given combination of Service Name, |
|
Disables the previously enabled trace for a given database session identifier (SID) on the local instance |
|
Enables the trace for a given database session identifier (SID) on the local instance |
111.1.1 CLIENT_ID_STAT_DISABLE Procedure
This procedure will disable statistics accumulation for all instances and remove the accumulated results from V$CLIENT_STATS
view enabled by the CLIENT_ID_STAT_ENABLE
Procedure.
Syntax
DBMS_MONITOR.CLIENT_ID_STAT_DISABLE( client_id IN VARCHAR2);
Parameters
Table 111-2 CLIENT_ID_STAT_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Client Identifier for which statistic aggregation is disabled |
Examples
To disable accumulation:
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE('janedoe');
111.1.2 CLIENT_ID_STAT_ENABLE Procedure
This procedure enables statistic gathering for a given Client Identifier.
V$CLIENT_STATS
views.
Syntax
DBMS_MONITOR.CLIENT_ID_STAT_ENABLE( client_id IN VARCHAR2);
Parameters
Table 111-3 CLIENT_ID_STAT_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Client Identifier for which statistic aggregation is enabled |
Examples
To enable statistic accumulation for a client with a given client ID:
EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('janedoe');
111.1.3 CLIENT_ID_TRACE_DISABLE Procedure
This procedure will disable tracing enabled by the CLIENT_ID_TRACE_ENABLE
Procedure.
Syntax
DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE( client_id IN VARCHAR2);
Parameters
Table 111-4 CLIENT_ID_TRACE_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Client Identifier for which SQL tracing is disabled |
Examples
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ('janedoe');
111.1.4 CLIENT_ID_TRACE_ENABLE Procedure
This procedure will enable the trace for a given client identifier globally for the database.
Syntax
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE( client_id IN VARCHAR2, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-5 CLIENT_ID_TRACE_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Database Session Identifier for which SQL tracing is enabled |
|
If |
|
If |
|
Frequency at which we dump row source statistics. Value should be ' |
Usage Notes
-
The trace will be written to multiple trace files because more than one Oracle shadow process can work on behalf of a given client identifier.
-
The tracing is enabled for all instances and persistent across restarts.
Examples
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('janedoe', TRUE, FALSE);
111.1.5 DATABASE_TRACE_DISABLE Procedure
This procedure disables SQL trace for the whole database or a specific instance.
Syntax
DBMS_MONITOR.DATABASE_TRACE_DISABLE( instance_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-6 DATABASE_TRACE_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Disables tracing for the named instance |
111.1.6 DATABASE_TRACE_ENABLE Procedure
This procedure enables SQL trace for the whole database or a specific instance.
Syntax
DBMS_MONITOR.DATABASE_TRACE_ENABLE( waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, instance_name IN VARCHAR2 DEFAULT NULL, plan_stat IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-7 DATABASE_TRACE_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
If |
|
If |
|
If set, restricts tracing to the named instance |
|
Frequency at which we dump row source statistics. Value should be ' |
111.1.7 SERV_MOD_ACT_STAT_DISABLE Procedure
This procedure will disable statistics accumulation and remove the accumulated results from V$SERV_MOD_ACT_STATS
view.
Statistics disabling is persistent for the database. That is, service statistics are disabled for instances of the same database (plus dblinks
that have been activated as a result of the enable).
Syntax
DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE( service_name IN VARCHAR2, module_name IN VARCHAR2, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
Parameters
Table 111-8 SERV_MOD_ACT_STAT_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the service for which statistic aggregation is disabled |
|
Name of the |
|
Name of the |
Usage Notes
Regarding statistics gathering, when you change the module or action, the change takes effect when the next user call is executed in the session. For example, if a module is set to 'module 1' in a session, and the module is reset to 'module 2' in a user call in the session, then the module remains 'module 1' during this user call. The module is changed to 'module 2' in the next user call in the session.
111.1.8 SERV_MOD_ACT_STAT_ENABLE Procedure
This procedure enables statistic gathering for a given combination of Service Name, MODULE
and ACTION
.
Calling this procedure enables statistic gathering for a hierarchical combination of Service name, MODULE
name, and ACTION
name on all instances for the same database. Statistics are accessible by means of the V$SERV_MOD_ACT_STATS
view.
Syntax
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( service_name IN VARCHAR2, module_name IN VARCHAR2, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
Parameters
Table 111-9 SERV_MOD_ACT_STAT_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the service for which statistic aggregation is enabled |
|
Name of the |
|
Name of the |
Usage Notes
Enabling statistic aggregation for the given combination of Service/Module/Action names is slightly complicated by the fact that the Module/Action values can be empty strings which are indistinguishable from NULLs. For this reason, we adopt the following conventions:
A special constant (unlikely to be a real action names) is defined:
ALL_ACTIONS constant VARCHAR2 := '###ALL_ACTIONS';
Using ALL_ACTIONS
for an action specification means that aggregation is enabled for all actions with a given module name, while using NULL
(or empty string) means that aggregation is enabled for an action whose name is an empty string.
Regarding statistics gathering, when you change the module or action, the change takes effect when the next user call is executed in the session. For example, if a module is set to 'module 1' in a session, and the module is reset to 'module 2' in a user call in the session, then the module remains 'module 1' during this user call. The module is changed to 'module 2' in the next user call in the session.
Examples
To enable statistic accumulation for a given combination of Service name and MODULE
:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE( 'APPS1','PAYROLL');
To enable statistic accumulation for a given combination of Service name, MODULE
and ACTION
:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE('APPS1','GLEDGER','DEBIT_ENTRY');
If both of the preceding commands are issued, statistics are accumulated as follows:
-
For the APPS1 service, because accumulation for each Service Name is the default.
-
For all actions in the
PAYROLL
Module. -
For the
DEBIT_ENTRY
Action within theGLEDGER
Module.
111.1.9 SERV_MOD_ACT_TRACE_DISABLE Procedure
This procedure will disable the trace at ALL
enabled instances for a given combination of Service Name, MODULE
, and ACTION
name globally.
Syntax
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE( service_name IN VARCHAR2, module_name IN VARCHAR2, action_name IN VARCHAR2 DEFAULT ALL_ACTIONS, instance_name IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-10 SERV_MOD_ACT_TRACE_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the service for which tracing is disabled. |
|
Name of the |
|
Name of the |
|
If set, this restricts tracing to the named |
Usage Notes
Specifying NULL
for the module_name
parameter means that statistics will no longer be accumulated for the sessions which do not set the MODULE
attribute.
Examples
To enable tracing for a Service named APPS1:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('APPS1', DBMS_MONITOR.ALL_MODULES, DBMS_MONITOR.ALL_ACTIONS,TRUE, FALSE,NULL);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE('APPS1');
To enable tracing for a given combination of Service
and MODULE
(all ACTION
s):
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('APPS1','PAYROLL', DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE('APPS1','PAYROLL');
111.1.10 SERV_MOD_ACT_TRACE_ENABLE Procedure
This procedure will enable SQL tracing for a given combination of Service Name, MODULE
and ACTION
globally unless an instance_name
is specified.
Syntax
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE( service_name IN VARCHAR2, module_name IN VARCHAR2 DEFAULT ANY_MODULE, action_name IN VARCHAR2 DEFAULT ANY_ACTION, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, instance_name IN VARCHAR2 DEFAULT NULL, plan_stat IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-11 SERV_MOD_ACT_TRACE_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the service for which SQL trace is enabled |
|
Name of the |
|
Name of the |
|
If |
|
If |
|
If set, this restricts tracing to the named |
|
Frequency at which we dump row source statistics. Value should be ' |
Usage Notes
-
The procedure enables a trace for a given combination of Service,
MODULE
andACTION
name. The specification is strictly hierarchical: Service Name or Service Name/MODULE
, or Service Name, MODULE, andACTION
name must be specified. Omitting a qualifier behaves like a wild-card, so that not specifying anACTION
means allACTION
s. Using theALL_ACTIONS
constant achieves the same purpose. -
This tracing is useful when an application
MODULE
and optionally knownACTION
is experiencing poor service levels. -
By default, tracing is enabled globally for the database. The
instance_name
parameter is provided to restrict tracing to named instances that are known, for example, to exhibit poor service levels. -
Tracing information is present in multiple trace files and you must use the
trcsess
tool to collect it into a single file. -
Specifying
NULL
for themodule_name
parameter means that statistics will be accumulated for the sessions which do not set theMODULE
attribute.
Examples
To enable tracing for a Service named APPS1
:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('APPS1', DBMS_MONITOR.ALL_MODULES, DBMS_MONITOR.ALL_ACTIONS,TRUE, FALSE,NULL);
To enable tracing for a given combination of Service and MODULE
(all ACTION
s):
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('APPS1','PAYROLL', DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
111.1.11 SESSION_TRACE_DISABLE Procedure
This procedure will disable the trace for a given database session at the local instance.
Syntax
DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 111-12 SESSION_TRACE_DISABLE Procedure Parameters
Parameter | Description |
---|---|
|
Database Session Identifier for which SQL trace is disabled |
|
Serial number for this session |
Usage Notes
If serial_num
is NULL
but session_id
is specified, a session with a given session_id
is no longer traced irrespective of its serial number. If both session_id
and serial_num
are NULL
, the current user session is no longer traced. It is illegal to specify NULL
session_id
and non-NULL
serial_num
. In addition, the NULL
values are default and can be omitted.
Examples
To enable tracing for a client with a given client session ID:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);;
111.1.12 SESSION_TRACE_ENABLE Procedure
This procedure enables a SQL trace for the given Session ID on the local instance
Syntax
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE, plan_stat IN VARCHAR2 DEFAULT NULL);
Parameters
Table 111-13 SESSION_TRACE_ENABLE Procedure Parameters
Parameter | Description |
---|---|
|
Client Identifier for which SQL trace is enabled. If omitted (or |
|
Serial number for this session. If omitted (or |
|
If |
|
If |
|
Frequency at which we dump row source statistics. Value should be ' |
Usage Notes
The procedure enables a trace for a given database session, and is still useful for client/server applications. The trace is enabled only on the instance to which the caller is connected, since database sessions do not span instances. This tracing is strictly local to an instance.
If serial_num
is NULL
but session_id
is specified, a session with a given session_id
is traced irrespective of its serial number. If both session_id
and serial_num
are NULL
, the current user session is traced. It is illegal to specify NULL
session_id
and non-NULL
serial_num
. In addition, the NULL
values are default and can be omitted.
Examples
To enable tracing for a client with a given client session ID:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);
Either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5);
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5, NULL);
traces the session with session ID of 5, while either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE();
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL);
traces the current user session. Also,
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL, TRUE, TRUE);
traces the current user session including waits and binds. The same can be also expressed using keyword syntax:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>TRUE);