Thursday, April 17, 2008

Diagnosing Production Java applications with AD4J

One of my applications that uses EJB 3 and JPA was running much slower today and I had no clue what was going on. It is a very simple application to manage service requests. When I was trying to update some data for a service request – it was taking ever. Also searching of service requests by some search criteria was taking much longer than it does.

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.

Downloading AD4J

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.

4 comments:

sari said...

Can you please help me how can i install ad4j console. From where I can get jamweb.msi file to install.

Atul Kumar said...
This comment has been removed by the author.
Atul Kumar said...

Sari,
You can download AD4J console from http://www.oracle.com/technology/software/products/oem/htdocs/jade.html

JVM & DB Agents are available within console


Atul Kumar
http://onlineAppsDBA.com

Venkat said...

I am trying to use AD4J for cross tier monitoring of APP/DB tiers. I have deployed both middle-tier and DB agents in the context of application. But in my case "DB Wait" link does not have any hyperlinl and hence not able to dig into database details of the active thread in JVM. Is there any special step I am missing to link middle-tier and database tiers for monitoring purpose.

Thanks
-Venkat