Resetting User Session Handle Attributes in JDBC

From Oracle Database 12.1 onward, the JDBC driver provides a convenient method called setClientInfo to set user session handle attributes in the database. There are three user session handle attributes that are useful when instrumenting your application so that it identifies itself and exposes its actions in Oracle Database (such as in the V$SESSION view):

• CLIENTID: Specifies the client identifier value for the connection and exposed as the CLIENT_IDENTIFIER column in V$SESSION. This is often a user name or a similar identifier.
• MODULE: Specifies the module value for the connection and exposed as MODULE column in V$SESSION. Typically a granular business grouping such as GL, AP, or PAYROLL. You might think of this as being similar to a namespace that exists in many programming languages.
• ACTION: Specifies the action value for the connection and exposed as the ACTION column in V$SESSION. A specific action within the business grouping such as MONTH_END, RECONCILIATION, or DISBURSEMENT.

While these are typical examples, you should always use meaningful values for the business task that your application supports.

Setting these attributes is the high-performance way of instrumenting a JDBC application that uses Oracle Database. You invoke the setClientInfo method on a Connection object in your application to set these attribute values. Such an application performs the following high-level steps:

1. Get a connection from the connection pool.
2. Invoke setClientInfo to set the user session handle attributes.
3. Perform an action such as a query that motivates a database round-trip thus setting the user session handle attributes in the database.
4. Carry out the remainder of the application business task.
5. Reset the user session handle attributes before placing the connection back into the pool.
6. Return the connection to the pool.

In Step 3 an action that motivates a database round-trip is performed and this is important when using setClientInfo. As a performance optimization the JDBC driver does not immediately send the attribute values to the database when setClientInfo is invoked. It “piggybacks” the values as part of a subsequent database call. This leads to higher performance by avoiding extra database calls and additional network round-trips.

The following Java code snippet illustrates the above steps for a fictitious General Ledger (GL) module performing the “JournalImport” action:

// A connection pool-aware data source interface from oracle.ucp.jdbc.
// PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
// Connection conn = pds.getConnection();
// Set user session handle attributes on the JDBC connection object.
conn.setClientInfo("OCSID.CLIENTID", "markwill");
conn.setClientInfo("OCSID.MODULE", "GL");
conn.setClientInfo("OCSID.ACTION", "JournalImport");
// The JournalImport code; the first dbcall here will set the user session handle attributes in the database.
// Reset user session handle attributes on the JDBC connection object.
conn.setClientInfo("OCSID.CLIENTID", null);
conn.setClientInfo("OCSID.MODULE", null);
conn.setClientInfo("OCSID.ACTION", null);
// Return the connection to the pool.
conn.close();

In this snippet it is clear that the intention is to reset (via setting to NULL) the user session handle attributes prior to returning the connection to the pool. However, if you use a query similar to the following you may get unexpected values for the CLIENT_IDENTIFIER, MODULE, and ACTION columns:

select     sid,
           serial#,
           service_name,
           machine,
           client_identifier,
           module,
           action
from       v$session
where      username = 'MARKWILL'
order by   machine,
           client_identifier,
           module,
           action,
           sid,
           serial#;

While the intention to reset the values to NULL is clearly stated in the Java code, the previous values persist in the database after the connection has been closed. With the Oracle JDBC driver the close call is not sufficient to motivate the required database round-trip such that the values are reset to NULL. There are options such as issuing a meaningless query (e.g., select dummy from dual) whose sole purpose is to force a round-trip, but that is undesirable.

The good news is that there is a method exposed by the connection object that results in a lightweight call to the database without resorting to a technique such as executing a meaningless query: isValid(int timeout). The isValid method returns true if the connection is in the open state and valid for use. A zero timeout parameter value indicates that no timeout value should be applied to the operation. Consult the JDBC documentation if needed.

Inserting a call to isValid just before returning the connection to the pool achieves the goal of resetting the user session handle attributes via a lightweight mechanism without the need for extra SQL. Here is the Java snippet again with this call inserted:

// A connection pool-aware data source interface from oracle.ucp.jdbc.
// PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
// Connection conn = pds.getConnection();
// Set user session handle attributes on the JDBC connection object.
conn.setClientInfo("OCSID.CLIENTID", "markwill");
conn.setClientInfo("OCSID.MODULE", "GL");
conn.setClientInfo("OCSID.ACTION", "JournalImport");
// The JournalImport code; the first dbcall here will set the user session handle attributes in the database.
// Reset user session handle attributes on the JDBC connection object.
conn.setClientInfo("OCSID.CLIENTID", null);
conn.setClientInfo("OCSID.MODULE", null);
conn.setClientInfo("OCSID.ACTION", null);
// Invoke isValid to motivate a lightweight dbcall.
// This will reset (set to null) the user session handle attributes in the database.
conn.isValid(0);
// Return the connection to the pool.
conn.close();

If you execute the sample query against V$SESSION after making this change, you should see that the values are now reset to NULL as desired in the Java code.