|
About
Oracle dbms_monitor
You can use the newly introduced Oracle
dbms_monitor package to control additional
tracing and statistics gathering. The Oracle
dbms_monitor package contains the following
procedures used to enable and disable
additional statistics aggregation:
Also see my notes on the
session_trace_enable procedure
and the
database_trace_enable procedure.
Here is an example to enable and disable the
tracing based on a client_id:
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE
('client id');
EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE
('client id');
The DBMS_MONITOR
package provides a useful method for
managing additional trace and statistics
gathering. This can be quite useful when one
wants to programmatically control tracing
from within PL/SQL code where DDL commands
like ALTER SESSION are supported only via
dynamic SQL.
client_id_stat_disable
is a procedure that disables or turns off
statistics accumulation for a user specified
client identifier.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
|
Table 7.212:
Client_id_stat_disable Parameter
client_id_stat_enable is a procedure that enables or
turns on statistics accumulation for a user
specified client identifier.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
|
Table 7.213:
Client_id_stat_enable Parameter
client_id_trace_disable is a procedure that disables
tracing for a user specified client
identifier.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
|
Table 7.214:
Client_id_trace_disable Parameter
client_id_trace_enable is a procedure that enables
tracing for a user specified client
identifier. The plan_stat
parameter valid values are NEVER,
FIRST_EXECUTION (same as NULL) and
ALL_EXECUTIONS.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
|
|
WAITS
|
BOOLEAN
|
IN
|
TRUE
|
|
BINDS
|
BOOLEAN
|
IN
|
FALSE
|
|
PLAN_STAT
|
VARCHAR2
|
IN
|
NULL
|
Table 7.215:
Client_id_trace_enable Parameters
database_trace_disable
is a procedure that disables tracing for a
user specified database instance.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
INSTANCE_NAME
|
VARCHAR2
|
IN
|
|
Table 7.216:
Database_trace_disable Parameter
database_trace_enable
is a procedure that enables or turns
tracing for a user specified database
instance. The plan_statparameter valid values are
NEVER, FIRST_EXECUTION (same as NULL) and
ALL_EXECUTIONS.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WAITS
|
BOOLEAN
|
IN
|
TRUE
|
|
BINDS
|
BOOLEAN
|
IN
|
FALSE
|
|
INSTANCE_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
PLAN_STAT
|
VARCHAR2
|
IN
|
NULL
|
Table 7.217:
Database_trace_enable Parameters
serv_mod_act_stat_disable is a procedure that disables
statistics accumulation for user specified
service, module and action. It also deletes
any previously accumulated statistics.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SERVICE_NAME
|
VARCHAR2
|
IN
|
|
|
MODULE_NAME
|
VARCHAR2
|
IN
|
|
|
ACTION_NAME
|
VARCHAR2
|
IN
|
ALL_ACTIONS
|
Table 7.218:
Serv_mod_act_stat_disable Parameters
serv_mod_act_stat_enable
is a procedure that enables statistics
accumulation for all instances represented
by the user specified service, module and
action.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SERVICE_NAME
|
VARCHAR2
|
IN
|
|
|
MODULE_NAME
|
VARCHAR2
|
IN
|
|
|
ACTION_NAME
|
VARCHAR2
|
IN
|
ALL_ACTIONS
|
Table 7.219:
Serv_mod_act_stat_enable Parameters
serv_mod_act_trace_disableis a procedure that disables
tracing for a user specified service, module
and action.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SERVICE_NAME
|
VARCHAR2
|
IN
|
|
|
MODULE_NAME
|
VARCHAR2
|
IN
|
|
|
ACTION_NAME
|
VARCHAR2
|
IN
|
ALL_ACTIONS
|
|
INSTANCE_NAME
|
VARCHAR2
|
IN
|
NULL
|
Table 7.220:
Serv_mod_act_trace_disable Parameters
serv_mod_act_trace_enable
is a procedure that enables tracing for a
user specified service, module and action.
The plan_stat
parameter valid values are NEVER,
FIRST_EXECUTION (same as NULL) and
ALL_EXECUTIONS.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SERVICE_NAME
|
VARCHAR2
|
IN
|
|
|
MODULE_NAME
|
VARCHAR2
|
IN
|
ANY_MODULE
|
|
ACTION_NAME
|
VARCHAR2
|
IN
|
ANY_ACTION
|
|
WAITS
|
BOOLEAN
|
IN
|
TRUE
|
|
BINDS
|
BOOLEAN
|
IN
|
FALSE
|
|
INSTANCE_NAME
|
VARCHAR2
|
IN
|
NULL
|
|
PLAN_STAT
|
VARCHAR2
|
IN
|
NULL
|
Table 7.221:
Serv_mod_act_trace_enable Parameters
session_trace_disable
is a procedure that disables tracing for a
user specified session. If both the session
ID and serial number parameters are NULL,
then that means the current session.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SESSION_ID
|
BINARY_INTEGER
|
IN
|
NULL
|
|
SERIAL_NUM
|
BINARY_INTEGER
|
IN
|
NULL
|
Table 7.222:
Session_trace_disable Parameters
session_trace_enable
is a procedure that enables tracing for a
user specified session. If both the session
ID and serial number parameters are NULL,
then that means the current session. The
plan_stat
parameter valid values are NEVER,
FIRST_EXECUTION and ALL_EXECUTIONS.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WAITS
|
BOOLEAN
|
IN
|
TRUE
|
|
BINDS
|
BOOLEAN
|
IN
|
FALSE
|
|
PLAN_STAT
|
VARCHAR2
|
IN
|
NULL
|
Table 7.223:
Session_trace_enable Parameters
|
|
 |
|
Inside the DBMS Packages
The DBMS packages form the foundation of
Oracle DBA functionality. Now, Paulo Portugal writes a landmark book
Advanced Oracle DBMS Packages: The Definitive Reference.
This is a must-have book complete with a code
depot of working examples for all of the major DBMS packages.
Order directly from Rampant and save 30%.
|
|
|
|
