Pass User and ApplicationModule Information to Database Session

In this blog, I show how to pass information from your ADF application to the DB session. This allows the DB administrator to identify problematic sessions on the DB side, like long-running SQL statements or long waiting sessions.

If you run a real-world application on a server, not necessarily an ADF application, you can run into problems with the DB sessions that can’t easily be tracked. Looking at the DB sessions, all information a DBA sees is some generic session information. If such a session is a long-running SQL statement or a session causing high CPU load on the DB, he can’t know who or what is causing the problem. The image below shows what a DBA normally sees when looking at the DB session:

All we see is that there are a couple of sessions running against the HR DB. If one or more of the sessions cause a problem on the DB side, it’s hard to diagnose and fix.

Problem Description

ADF applications are using one to many application modules that are connected to a DB resulting in DB sessions. In general, each root application module uses one DB connection attached to a DB session. If you use ADF task flows using their own transaction, this can create connections to the DB, too.

The sample application used for the image above consists of two root application modules and one stacked application module. Stacked means an application module that is a sibling of another application module as shown below

If you look at the data controls, you see two root application modules (the root nodes) BDBIFAAppModuleDataControl’ and ‘EmpAppLibModuleDataControl’. Inside ‘BDBIFAAppModuleDataControl’, we see the stacked application modules ‘DepLibAppModule1’ and ‘EmpLibAppModule1’.

The UI uses two root modules and one stacked application module:

The button above the tables prints the name of the application module and the ID of the DB session attached to the application module.

You see that the first two tables use a different application module, but the same DB session (same ID). They are sharing their transaction. The last table uses its own application module, DB session (different ID), and transaction.
The sample uses ADF security. When multiple users run the application, you see more DB sessions. Running the app with two users will result in 4 sessions:

The 5th session is the one used to get the information from a SQL Developer report. To track down problems from the DB side, it would help to know which users using which application module cause the trouble. Together with the time of the problem, it is easy to find the problematic code.

Solution

If you look at the ‘Sessions’ report from SQL Developer above, you’ll notice two columns:

These columns can be used to pass information to the session about the client who created the session in the DB. ADF doesn’t pass any information here by default. However, it’s possible to change this.
A client, in our case the ADF application, can call two DB procedures to set information to the two mentioned columns:

dbms_session.set_identifier(?) and
dbms_application_info.set_client_info(?)

The first procedure is used to set the user name, the second to add information about the application module that’s connected to the session.
Now, the only thing to do is to call the two procedures at the right time in the lifecycle of an application module.
The right method to use is the

prepareSession(Session session)

method of the ApplicationModuleImpl class. Overwriting this method allows us to add code to set the current user name and the application module that is attached to the session.

@Override
protected void prepareSession(Session session) {
    super.prepareSession(session);
    DBTransaction tx = getDBTransaction();
    // für Root AM setzen wir den angemeldeten USer in die DB session
    if (isRoot()) {
        String user = getUserPrincipalName();
        if (user == null) {
            // if no security given, we user anonymous as username
            user = "anonymous";
        }

        String module = this.getTransactionName();

        callStoredProcedure(tx, Types.NULL, "dbms_session.set_identifier(?)", new Object[] { user });
        callStoredProcedure(tx, Types.NULL, "dbms_application_info.set_client_info(?)", new Object[] { module });
    }
}

The convenience method callStoredProcedure(…) you’ll find in the source code for the sample. The download instructions are at the end of the post.

Finally, running the sample with the overwritten prepareSession method we get for user ‘timo’:

And for user ‘steve’ in a different browser window or client machine:

In SQL Developer, the ‘Sessions’ report shows:

The user and the application module information is visible in the ‘CLIENT_INFO’ and ‘CLIENT_ID’ columns. This information greatly helps to track down problems on the DB side of the application.

Download Sample Application

You can download the sample application from GitHub BlogAppInfoToDB or the ZIP file. The sample uses the HR DB schema. For the ADF security, two users are defined: ‘timo’ and ‘steve’, both using ‘welcome1’ as a password. The sample was developed using JDeveloper 12.2.1.4.