How to trace the connection pool in a Java Web application - DBMS_APPLICATION_INFO

Question

Hello,

I need improve the traceability in a Web Application that usually run on fixed db user. The DBA should have a fast access for the information about the heavy users that are degrading the database.

5 years ago, I implemented a .NET ORM engine which makes a log of user and the server using the DBMS_APPLICATION_INFO package. Using a wrapper above the connection manager with the following code:

DBMS_APPLICATION_INFO.SET_MODULE('" + User + " - " + appServerMachine + "','');

Each time that a connection get a connection from the pool, the package is executed to log the information in the V$SESSION.

Has anyone discover or implemented a solution for this problem using the Toplink or Hibernate? Is there a default implementation for this problem?

I found here a solutions as I implemented 5 years ago, but I'd like to know with anyone have a better solution and integrated with the ORM.

http://stackoverflow.com/questions/53379/using-dbmsapplicationinfo-with-jboss

My application is above Spring, the DAO are implemented with JPA (using hibernate) and actually running directly in Tomcat, with plans to (next year) migrate to SAP Netwevare Application Server.

Thanks.

Answer 1

You should be able to configure a logger (eg log4j) on your connection pool. You may need a custom appender to pull back the user ID.

Two points to consider:

1) On a busy system this will generate a big log file.

2) Frequent connections are not necessary an indication of behaviour that would degrade the DB.

Answer 2

If you want to know about queries that are costing a lot to run, you should go directly into your database server. There are monitoring tools for that, specific to each server. For example in PostgreSQL you would run SELECT * FROM pg_stat_activity as an admin to check each connection and what it's doing, how long it's been running, etc.

If you really want to/need to do it from the container, then maybe you can define an interceptor with Spring AOP to execute that statement you need before doing anything. Keep in mind that a database connection is not always used by the same application user, since you're using a pool.

Answer 3

In Oracle 10g we can use DBMS_SESSION.SET_IDENTIFIER to uniquely identify a specific session. Oracle also provide a JDBC built-in to hook this into a connection pool. You will have to provide your own means of uniquely identifying a session, which will depend on your application.

Your DBA will then have enough information to identify the resource hungry session.

No DBA I know would be impressed with a huge text file generated from the middle tier.