You should set OCSID.CLIENTID

each time you grab an Oracle JDBC connection from the pool

For troubleshooting and monitoring performance, you want to follow what happens from the end-user to the database. It is then mandatory to identify the end-user and application from the database session. With Oracle there are some ‘dbms_application_info’ strings to be set, like MODULE, ACTION and CLIENT_INFO. That’s about the tasks in the application code (like identifying the Java class or method from which the SQL statement is prepared) but that’s not about the end-user.

And you should forget about the CLIENT_INFO which is not very useful and rather misleading. OCSID.MODULE and OCSID.ACTION are set from JDBC with Connection.setClientInfo (One reason I find the CLIENT_INFO name misleading is that it cannot be set with setClientInfo). Of course, you can also call ‘dbms_application_info.set_module’ but that’s an additional call to the database (which means network latency, OS context switch,…). Using the JDBC setClientInfo with the OCSID namespace sends this information with the next call.

Now, about identifying the end-user, there’s the session CLIENT_ID (aka CLIENT_IDENTIFIER) that you can also set with Connection.setClientInfo (OCSID.CLIENTID). This one is visible in many Oracle views and follows the database links. Here is an example, I create a demo user and a database link:

connect sys/oracle@//localhost/PDB1 as sysdba
drop public database link PDB1@SYSTEM;
grant dba to demo identified by demo;
create public database link PDB1@SYSTEM connect to SYSTEM
 identified by oracle using '//localhost/PDB1';

The following JavaScript (run from SQLcl) connects with a JDBC Thin driver, sets OCSID.MODULE, OCSID.ACTION and OCSID.CLIENTID, and displays CLIENT_IDENTIFIER, MODULE and ACTION from V$SESSION:

script
var DriverManager = Java.type("java.sql.DriverManager");
var con = DriverManager.getConnection(
 "jdbc:oracle:thin:@//localhost/PDB1","demo","demo"
);
con.setAutoCommit(false);
function showSessionInfo(){
 var sql=con.createStatement();
 var res=sql.executeQuery("\
  select client_identifier,service_name,module,action,value \
  from v$session \
  join v$mystat using(sid) \
  join v$statname using(statistic#) \
  where name='user calls' \
 ");
 while(res.next()){
  print();
  print(" CLIENT_IDENTIFIER: "+res.getString(1));
  print(" SERVICE:           "+res.getString(2));
  print(" MODULE:            "+res.getString(3));
  print(" ACTION:            "+res.getString(4));
  print(" User Calls:        "+res.getInt(5));
  print();
 }
}
showSessionInfo();
con.setClientInfo('OCSID.CLIENTID','my Client ID');
con.setClientInfo('OCSID.MODULE','my Module');
con.setClientInfo('OCSID.ACTION','my Action');
showSessionInfo();
// run a statement through DBLINK:
var sql=con.createStatement();
sql.executeUpdate("call dbms_output.put_line@PDB1@SYSTEM(null)");

I also display the ‘user calls’ from V$MYSTAT. Here is the output:

SQL> .
CLIENT_IDENTIFIER: null
 SERVICE:           pdb1
 MODULE:            JDBC Thin Client
 ACTION:            null
 User Calls:        4
CLIENT_IDENTIFIER: my Client ID
 SERVICE:           pdb1
 MODULE:            my Module
 ACTION:            my Action
 User Calls:        5

The second execution sees the MODULE, ACTION and CLIENT_IDENTIFIER set with the previous setClientInfo(). And the most important is that the ‘user calls’ statistic has been incremented only by one, which means that setting them did not add any additional roundtrips to the database server.

Now, after the call through database link, I display all user sessions from V$SESSION. I can see my SQLcl (java) with nothing set, the JDBC thin session with MODULE, ACTION and CLIENT_IDENTIFIER, and the DBLINK session (connected to SYSTEM) with only the CLIENT_IDENTIFIER set:

SQL> select username,client_identifier,module,action
  2   from v$session where type='USER';
  USERNAME   CLIENT_IDENTIFIER                   MODULE       ACTION
__________ ___________________ ________________________ ____________
SYSTEM     my Client ID        oracle@db192
SYS                            java@db192 (TNS V1-V3)
DEMO       my Client ID        my Module                my Action

Following the end-user down to all layers (application, database, remote databases) is great for end-to-end troubleshooting and performance analysis. Set this OCSID.CLIENTID to identify the application (micro-)service and the end-user (like a browser Session ID), for no additional cost, and you will find this information in many performance views:

select table_name, listagg(distinct column_name,', ') 
 within group (order by column_name)
 from dba_tab_columns
 where column_name in ('CLIENT_IDENTIFIER','CLIENT_INFO','CLIENT_ID','MODULE','ACTION')
 --and table_name like 'GV%'
 group by table_name
 order by 2;

You see how the ‘CLIENT_INFO’ is useless (except for an additional level to module/action for SQL Monitor) and how CLIENT_ID(ENTIFIER) is everywhere, including ASH (Active Session history).

With a micro-services architecture, you will have many connections to the database (don’t tell me that each microservice has its own database — databases were invented decades ago when streaming data everywhere was an un-maintainable/un-scalable/errorprone mess, and schemas and views were invented to provide this data-micro-services within the same database system). Then the best practice is to:

• connect with a dedicated SERVICE_NAME
• identify the end-user with a CLIENT_ID

and then end-to-end tracing, tuning and troubleshooting will become easy.