• Welcome
• Projects
• Presentations
• Research

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE Procedure

This procedure, which was introduced in Oracle 10.1, enables event 10046 trace for all sessions with the specified client ID.

The CLIENT_ID must be specified, but does not need to belong to a current session at the time this procedure is invoked.

There are two optional Boolean parameters, WAITS and BINDS, both of which default to FALSE.

In Oracle 11.1 and above PLAN_STAT specifies the dump frequency for row source (STAT) statistics. Values can be:

• NEVER
• FIRST EXECUTION (default)
• ALL_EXECUTIONS

PLAN_STAT is useful when individual statement executions are affected by data cardinality / selectivity

The client identifier was introduced to provide a method of uniquely identifying sessions when many connect using the same Oracle user. This frequently occurs systems which include mid-tier servers or connection pools.

The client identifier can be set for a session using the SET_IDENTIFIER procedure in the DBMS_SESSION package. For example:

EXECUTE DBMS_SESSION.SET_IDENTIFIER ('ID1');

The client identifier is externalised in the CLIENT_IDENTIFIER column of the V$SESSION dynamic performance view. For example:

SELECT sid FROM v$session WHERE client_identifier = 'ID1';

For example to enable event 10046, level 1 trace for CLIENT_ID 'ID1' use:

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1');

To enable event 10046, level 4 trace (bind variables) for CLIENT_ID 'ID1' use:

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',binds=>true);

To enable event 10046, level 8 trace (waits) for CLIENT_ID 'ID1' use:

EXECUTE dbms_monitor.client_id_trace_enable (client_id=>'ID1',waits=>true);

The current state of client_id trace is reported in the data dictionary view DBA_ENABLED_TRACES. This contains the following columns:

For client_id trace, the TRACE_TYPE column will be 'CLIENT_ID' and the PRIMARY_ID column will contain the client identifier.

The DBA_ENABLED_TRACES view is based on the underlying data dictionary table SYS.WRI$_TRACING_ENABLED.