How to trace a java application through a connection pool using DBMS_MONITOR

Much has been written on how to trace database sessions through a java application in all sort of different techniques.
Since Oracle 10g a new technique is added to the tracing stack: DBMS_MONITOR. This blog briefly describes with code examples
how you could use DBMS_MONITOR to trace database sessions via a web application, through a pooled connection

So, why is there so much fuzz about tracing database sessions through a java application? In the old days, on client server applications
each user had his/her own databsae session. So, tracing a user was rather straight forward. When the DBA knew the user name, he/she knew
what session to trace. However, a java application generally has a connection pool through with users are connected to a database,
so when you try to trace a session the old way, using alter session set trace=true, you will not only get a trace of that user, but a trace
of all users using that particular session in time. In addition, the trace of one user can get filed in multiple trace files, because
the user might switch from one connection to a different connection.

To overcome this issue, you must have an opportunity to trace multiple sessions belonging to the same user actions. Since Oracle 10g
you can use DBMS_MONITOR for the job at hand. Below is a short how-to. I’ve created an example where CLIENT-ID is used to collect all traces
belonging to one user via something called “clinent-ID”. This client-ID is set in the database via the web application. From then on a DBA
is able to monitor the user via this client-ID. It involves the creation of a stored procedure to set the
client-ID, a call to this stored procedure in an overridden class of the ADF-BC ApplicationModuleImpl.class

* Create a stored procedure (or better yet, a packaged procedure) to set the client identifier, using DBMS_SESSION.SET_CLIENT_ID

  create or replace procedure set_cid(p_cid varchar2)
  is
  begin
    sys.dbms_session.set_client_identifier(p_cid);
  end set_cid;

* In your java program set the client-ID, like in the example code fragment: the current user, by invoking the stored procedure
every time the application module is invoked. This is equivalent to setting a context if for example you would set a virtual
private database (VPD) context. In ADF-BC this typically is done by extending class ApplicationModuleImpl.

    public class MyApplicationModuleImpl extends ApplicationModuleImpl {
      ...
    protected void prepareSession(Session session) {

      super.prepareSession(session);
      SessionImpl ses = (SessionImpl)getDBTransaction().getSession();
      String currentUser  = ses.getUserPrincipalName();

      PreparedStatement st = null;
      try {
        st = getDBTransaction().createPreparedStatement("begin set_cid(?); end;", 0);
        st.setObject(0, currentUser);
        }
        st.executeUpdate();
      }
      catch (SQLException e) {
        throw new Exception(e);
      }
    }

* Next step is deploying and starting the web application.

* The user can now log into web application. For example, login user = johndoe.
Remember, as soon as the user connects to the database, via MyApplicationModuleImpl.class, stored procedure set_sid
is invoked with the username (johndoe) as argument. resulting in setting the client-ID in the database session.

* In another database session (i.e. SQL*Plus) the DBA can now start monitoring the application.
First step is querying v$session to obtain a list of current sessions and client-ID’s.

select sid, serial, client_identifier, username from v$session where client-ID = 'johndoe';

The query shows all sessions with client-ID = johndoe. This client-ID is in turn input for
starting the trace, using the simplest form:

exec dbms_monitor.client_id_trace_enable('johndoe')

Now you are tracing all sessions that are related to the given client-ID. Even if, in the connection pool,
the user is switching sessions.

* Run your java program under the required user. The user sessions are now traced.

* Stop your java program. Time statistics will only get gathered after all sessions are stopped. The same behaviour

* Run trcsess for all tracefiles that might contain traces for clientID. trcsess will browse and gather through all
trace files which name is like pgio_ora_*.trc searching for anything that corresponds to the client-ID.

trcsess output=johndoe.trc clientid=johndoe pgio_ora_*.trc

* Run TKPROF, like you would in the good ol’ days. For brievity, I will invoke the most simplest use.

tkprof johndoe.trc tkprof-report.txt

* View and interprete the tkprof report as you did before.
In addition, using DBMS_MONITOR offers additions data dictionary views in which session characteristics like CPU time,
event times etc are aggregated.