So I thought I would give Oracle AD4J a spin and will try to find out what is going! It is good that Oracle AD4J does not require any code changes in application, because it does not use byte code instrumentation. Nor does it require to restart/redeploy the application – so it was a perfect fit.
And it really helped diagnose the problem by me get into bottom of the issues. It was indeed a DB issue as I had initially suspected.
I will not bore you with installation and configuration steps of AD4J – rather I will outline how AD4J helped me diagnose the issue.
Finding the Offending Thread
As soon as I logon to AD4J console and clicked to view active threads – it showed all threads with the correct state and I saw one thread is with status “DBWait”.
That gave me an indication that something going fishy in the database request from from JPA provider.
Finding DB issue
When I clicked on DB Wait link to view details, it became clear that the transaction is running into some row lock contention with another user session.
Finding the SQL statement
Interestingly it allowed to me to view the SQL Statement that is taking forever by clicking the SQL hash. My application uses JPA 1.0 and I could view the SQL statements causing problem without increasing the log level from my JPA provider as in the following figure:
It also helped me find the other session that held the lock. Apparently we found that it was a batch process that was hanging for some reasons. After the hanging session was killed the application ran like a champ again!
Also I found that the JPQL query for retrieving SRs was running slower because of some missing indexes on the column and it was making a full table scan. Nice, it also AD4J allows to run explain for sql statements.
You can download AD4J from OTN (http://www.oracle.com/technology/software/products/oem/htdocs/jade.html)
Installing AD4J Console and agents
Installing AD4J Console is easy. You can follow this guide to do this installation of AD4J Console and agent.
After you deploy the agent you are good to go. Your JVM for application server will be automatically discovered in the console.
The AD4J agent is a WAR file that needs to be deployed to the server. You can follow the instructions in the install guide to install the agent in favorite application server. I was using my favorite container OC4J, however it supports BEA WebLogic, IBM Websphere, JBoss, and Tomcat. You can run AD4J also with standalone Java.
If you want to trace the problem to the DB tier you can install the DB Agent. You can follow the instructions in this guide.
I will play around with AD4J and blog little more about its feature in future. In the meantime you can review it's usage scenario documented in here.