Database Reference
In-Depth Information
Component Level
To enable and disable SQL trace for a component specified through a service name, module name, and action name,
the
dbms_monitor
package provides the
serv_mod_act_trace_enable
and
serv_mod_act_trace_disable
procedures,
respectively. To take full advantage of these procedures, you have to set the session attributes, module name, and
action name.
The following PL/SQL call enables SQL trace at level 28 for all sessions using the attributes specified as a
parameter:
dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM11203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
waits => TRUE,
binds => TRUE,
instance_name => NULL,
plan_stat => 'all_executions')
The only parameter without a default value is the first:
service_name
.
2
The default values of the
module_name
and
action_name
parameters are
any_module
and
any_action
, respectively. For both,
NULL
is a valid value. If the
action_name
parameter is specified, you must specify the
module_name
parameter as well. Failing to do so will result
in an ORA-13859 being raised. If Real Application Clusters is used, with the
instance_name
parameter it's possible to
restrict the tracing to a single database instance. By default, SQL trace is enabled for all database instances. Be aware
that the parameters
service_name
,
module_name
,
action_name
, and
instance_name
are case sensitive.
Because the setting is stored in the data dictionary, it persists across database instance restarts.
As for SQL trace at the client level, the
dba_enabled_traces
and, in a 12.1 multitenant environment,
cdb_enabled_traces
views display which component SQL trace has been enabled for, and which parameters were
used to enable it, through the
serv_mod_act_trace_enable
procedure. After enabling SQL trace with the previous
PL/SQL call, you get the following information:
SQL> SELECT primary_id AS service_name, qualifier_id1 AS module_name,
2 qualifier_id2 AS action_name, waits, binds, plan_stats
3 FROM dba_enabled_traces
4 WHERE trace_type IN ('SERVICE', 'SERVICE_MODULE', 'SERVICE_MODULE_ACTION');
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS PLAN_STATS
--------------------- ----------- ----------- ----- ----- ----------
DBM10203.antognini.ch mymodule myaction TRUE TRUE ALL_EXEC
Note that depending on the attributes (that is, the service name, module name, and action name) specified
as parameters to enable SQL trace, the
trace_type
column is set to either
SERVICE
,
SERVICE_MODULE
, or
SERVICE_MODULE_ACTION
.
The following PL/SQL call disables SQL trace for all sessions using the session attributes specified as parameters:
dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM11203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL)
