I have mentioned previously about the session variables MODULE and ACTION. Just to recap, these are simply string variables that you can set to reflect what activity it is performing. There’s another variable that I didn’t mention in the previous post; Client Identfier.
Like both MODULE and ACTION, the client identifier is a string variable that can be set to whatever value makes sense according to how you intend to use the variable. The maxmum length for the client identifier is 64 bytes and the value is set by calling the routine DBMS_SESSION.SET_IDENTIFIER, e.g.:
BEGIN
dbms_session.set_identifier ('my identifier');
END;
The current client identifier value can be accessed via the function call:
SYS_CONTEXT('userenv','client_identifier')
So, how should the client identifier be used? Well, for starters it’s probably best to understand where it’s exposed and what functionality can be tied to it. We can easily query the data dictionary to determine where the value is exposed. The variable is either named CLIENT_ID or CLIENT_IDENTIFIER:
SELECT owner
, table_name
, column_name
FROM dba_tab_columns
WHERE column_name IN ('CLIENT_ID','CLIENT_IDENTIFIER')
ORDER BY
owner
, table_name
On my 11.2 installation on Windows, this query displayed the following:
OWNER TABLE_NAME COLUMN_NAME ---------- ------------------------------ ------------------------------ SYS ALL_SCHEDULER_JOBS CLIENT_ID SYS ALL_SCHEDULER_JOB_LOG CLIENT_ID SYS ALL_SCHEDULER_WINDOW_LOG CLIENT_ID SYS DBA_AUDIT_EXISTS CLIENT_ID SYS DBA_AUDIT_OBJECT CLIENT_ID SYS DBA_AUDIT_SESSION CLIENT_ID SYS DBA_AUDIT_STATEMENT CLIENT_ID SYS DBA_AUDIT_TRAIL CLIENT_ID SYS DBA_COMMON_AUDIT_TRAIL CLIENT_ID SYS DBA_FGA_AUDIT_TRAIL CLIENT_ID SYS DBA_HIST_ACTIVE_SESS_HISTORY CLIENT_ID SYS DBA_SCHEDULER_JOBS CLIENT_ID SYS DBA_SCHEDULER_JOB_LOG CLIENT_ID SYS DBA_SCHEDULER_WINDOW_LOG CLIENT_ID SYS GLOBAL_CONTEXT CLIENT_IDENTIFIER SYS GV_$ACTIVE_SESSION_HISTORY CLIENT_ID SYS GV_$CLIENT_STATS CLIENT_IDENTIFIER SYS GV_$GLOBALCONTEXT CLIENT_IDENTIFIER SYS GV_$LOGMNR_CONTENTS CLIENT_ID SYS GV_$SESSION CLIENT_IDENTIFIER SYS GV_$SQL_MONITOR CLIENT_IDENTIFIER SYS KET$_CLIENT_CONFIG CLIENT_ID SYS KET$_CLIENT_TASKS CLIENT_ID SYS SCHEDULER$_EVENT_LOG CLIENT_ID SYS SCHEDULER$_JOB CLIENT_ID SYS SCHEDULER$_LIGHTWEIGHT_JOB CLIENT_ID SYS USER_AUDIT_OBJECT CLIENT_ID SYS USER_AUDIT_SESSION CLIENT_ID SYS USER_AUDIT_STATEMENT CLIENT_ID SYS USER_AUDIT_TRAIL CLIENT_ID SYS USER_SCHEDULER_JOBS CLIENT_ID SYS USER_SCHEDULER_JOB_LOG CLIENT_ID SYS V_$ACTIVE_SESSION_HISTORY CLIENT_ID SYS V_$CLIENT_STATS CLIENT_IDENTIFIER SYS V_$DIAG_ALERT_EXT CLIENT_ID SYS V_$GLOBALCONTEXT CLIENT_IDENTIFIER SYS V_$LOGMNR_CONTENTS CLIENT_ID SYS V_$SESSION CLIENT_IDENTIFIER SYS V_$SQL_MONITOR CLIENT_IDENTIFIER SYS WRH$_ACTIVE_SESSION_HISTORY CLIENT_ID SYS WRH$_ACTIVE_SESSION_HISTORY_BL CLIENT_ID SYSMAN MGMT_USER_CONTEXT CLIENT_IDENTIFIER
Note: in the above listing the objects with names starting GV_$ and V_$ are the actual objects names for the equivalent GV$ and V$ names that are commonly used. The commonly used GV$ / V$ are actually synonyms, e.g. V$SESSION is a synonym that references the V_$SESSION object.
Of particular interest is that client identifier appears in V$SESSION, V$ACTIVE_SESSION_HISTORY and V$SQL_MONITOR, which makes it very attractive for monitoring purposes.
So, what value should the client identifier be set to? On the assumption that you are using MODULE and ACTION to track the part of the code a process is executing then I would suggest that client identifier be used to denote the instance of execution. I have worked with systems that have set the client identifier to:
- A concatenation of various client details, including the end user network username and workstation, to get a value that can be tied to a specific end-user’s activity
- A GUID to get a unique business process execution that is propagated across the application tiers
Setting the client identifier to reflect the end user initiating the database process provides an easy mechanism to trace individual user activity within the database. Modern applications rarely have separate database accounts for each user, which means that when you are required to investigate the actions of a specific user then it can be difficult to figure out which database session the user was connected to. With the client identifier set to reflect the user then obtaining a history of user activity from Active Session History can be as simple as:
SELECT * FROM v$active_session_history WHERE client_id = <<user>>
Furthermore, SQL trace can be enabled on sessions according to the client identifier using the routine DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE. This can be particularly useful in those situations where you have a user complaining that the system is consistently giving them trouble even though no one else seems to be experiencing any problems. Tracing can be disabled using DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE.
Additionally, DBMS_MONITOR.CIENT_ID_STAT_ENABLE will start the collecting of statistics for the client identifier provided. These statistics are exposed in the view V$CLIENT_STATS. The statistics available are a bit of a mix of the details exposed in V$SESSSTAT and V$SESS_TIME_MODEL.This provides for an easy way to measure how busy specific users are keeping the database, potentially tying into why they may be experiencing performance troubles. By way of an illustration, we can measure the logical reads used by all activity under a specific client identifier as follows. First we enable statistics collection for the client identifier:
EXEC dbms_monitor.client_id_stat_enable ('session_abc')
The above can be done in any session at any time prior to when you’d like to collect statistics in any session with privileges to run DBMS_MONITOR. We then run a simple workload of creating a table under the client identifier:
SQL> EXEC dbms_session.set_identifier ('session_abc')
PL/SQL procedure successfully completed.
SQL> SELECT stat_name
2 , value
3 FROM v$client_stats
4 WHERE client_identifier = SYS_CONTEXT('userenv','client_identifier')
5 AND stat_name = 'session logical reads'
6 /
STAT_NAME VALUE
------------------------- ----------
session logical reads 0
SQL> CREATE TABLE stats_test
2 (id NUMBER(10) NOT NULL
3 ,cat NUMBER(2) NOT NULL
4 ,padding CHAR(30) NOT NULL
5 )
6 /
Table created.
SQL> INSERT INTO stats_test
2 WITH generator AS
3 (SELECT --+materialize
4 ROWNUM AS rn
5 FROM dual
6 CONNECT BY ROWNUM <= 1000)
7 SELECT ROWNUM
8 , MOD(ROWNUM,10)
9 , 'X'
10 FROM generator g1
11 , generator g2
12 /
1000000 rows created.
SQL> EXEC dbms_stats.gather_table_stats ('','STATS_TEST')
PL/SQL procedure successfully completed.
SQL> SELECT stat_name
2 , value
3 FROM v$client_stats
4 WHERE client_identifier = SYS_CONTEXT('userenv','client_identifier')
5 AND stat_name = 'session logical reads'
6 /
STAT_NAME VALUE
------------------------- ----------
session logical reads 51706
So, we can see that our table creation process performed 51,706 logical reads. You can also see that we can determine the current client identifier using the call to SYS_CONTEXT(‘userenv’,’client_identifier’). Lastly, we probably want to disable statistics collection on the client identifier:
EXEC dbms_monitor.client_id_stat_disable ('session_abc')
The client identifier provides for a particularly useful mechanism for tracking individual user activity within the database. It is easy to establish and exposed in many locations within the database. The hardest aspect of utilising it is coming up with a strategy for setting it. However, once an application has been configured to utilise client identifier then tracking individual activity becomes quite a trivial activity.
Mark Hoxey 