This procedure, which was introduced in Oracle 10.1, enables statistics collection for all sessions with the specified client ID.
| Argument Name | Type | In/Out | Default? |
| CLIENT_ID | VARCHAR2 | IN |
The CLIENT_ID must be specified, but does need to not belong to a current session at the time this procedure is invoked.
The client identifer 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 statistics collection trace for CLIENT_ID 'ID1' use:
EXECUTE dbms_monitor.client_id_stat_enable (client_id=>'ID1');
Client ID statistics are reported in the V$CLIENT_STATS dynamic performance view. This view has the following columns:
| Name | Type |
| CLIENT_IDENTIFIER | VARCHAR2(64) |
| STAT_ID | NUMBER |
| STAT_NAME | VARCHAR2(64) |
| VALUE | NUMBER |
This view reports a limited subset of statistics. In Oracle 10.2 these include the following:
| Statistic Name |
| DB_CPU |
| DB time |
| application wait time |
| cluster wait time |
| concurrency wait time |
| db block changes |
| execute count |
| gc cr block receive time |
| gc cr blocks received |
| gc current block receive time |
| gc current blocks received |
| opened cursors cumulative |
| parse count (total) |
| parse time elapsed |
| physical reads |
| physical writes |
| redo size |
| session cache cursor hits |
| session logical reads |
| sql execute elapsed time |
| user I/O wait time |
| user calls |
| user commits |
| user rollbacks |
| workarea executions - multipass |
| workarea executions - onepass |
| workarea executions - optimal |