Client Identifier

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s