This procedure, which was introduced in Oracle 10.1, enables event 10046 trace for all sessions with the specified client ID.
Argument Name | Type | In/Out | Default? |
CLIENT_ID | VARCHAR2 | IN | |
WAITS | BOOLEAN | IN | DEFAULT |
BINDS | BOOLEAN | IN | DEFAULT |
PLAN_STAT | VARCHAR2 | IN | DEFAULT |
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:
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:
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) |
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.