This procedure, which was introduced in Oracle 10.2, enables event 10046 trace for the entire database or in a RAC environment for the specified instance
Argument Name | Type | In/Out | Default? |
WAITS | BOOLEAN | IN | DEFAULT |
BINDS | BOOLEAN | IN | DEFAULT |
INSTANCE_NAME | VARCHAR2 | IN | DEFAULT |
PLAN_STAT | VARCHAR2 | IN | DEFAULT |
There are two optional Boolean parameters, WAITS and BINDS, both of which default to FALSE.
There is a third optional parameter, INSTANCE_NAME, which defaults to NULL.
If the INSTANCE_NAME is NULL then trace is enabled for all sessions in the database
If the INSTANCE_NAME is NOT NULL then trace is enabled for all sessions in the specified instance.
This functionality was not previously available in a clustered database environment.
In Oracle 11.1 and above PLAN_STAT specifies the dump frequency for row source (STAT) statistics. Values can be:
PLAN_STAT is useful when individual statement executions are affected by data cardinality / selectivity
In Oracle 10.2.0.1 (on Linux at least), take care when using this procedure in a RAC environment. If you use this procedure to enable instance-wide trace, you may not be able to disable it again. This appears to be a bug and can result in the rapid generation of significant amounts of trace. The only workaround appears to be to update the base table in the data dictionary (SYS.WRI$_TRACING_ENABLED) which will not be supported by Oracle.
For example to enable event 10046, level 1 trace for all sessions in the database use:
EXECUTE dbms_monitor.database_trace_enable
In a single-instance database, this is similar to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 1';
To enable event 10046, level 4 trace (bind variables) for all sessions in the database use:
EXECUTE dbms_monitor.database_trace_enable (binds=>true);
In a single-instance database, this is similar to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';
To enable event 10046, level 8 trace (waits) for all sessions in the database use:
EXECUTE dbms_monitor.database_trace_enable (waits=>true);
In a single-instance database, this is similar to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';
To enable event 10046, level 1 trace for all sessions in instance RAC1 use:
EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1');
In the current instance this is equivalent to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 1';
To enable event 10046, level 4 trace (bind variables) for all sessions in instance 'RAC1' use:
EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1',binds=>true);
In the current instance this is equivalent to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';
To enable event 10046, level 8 trace (waits) for all sessions in instance 'RAC1' use:
EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1',waits=>true);
In the current instance this is equivalent to:
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';
The current state of database and instance 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) |
The DBA_ENABLED_TRACES view is based on the underlying data dictionary table SYS.WRI$_TRACING_ENABLED.