Using the RRA trace group in WebSphere Application Server to determine elapsed time for SQL operations
JohnPape 0600007J6A Visits (10038)
Have you ever suspected that your JDBC operations might be causing some performance problems in your application? One easy way to examine your system for lengthy JDBC operations is to utilize the PMI system in WebSphere Application Server. PMI or Performance Monitoring Infrastructure is a service provided in the WebSphere run time to collect performance-related metrics for administrator review. While there are suitable metrics provided by PMI to tell you how long your JDBC calls are taking on average, it cannot tell you the exact elapsed time for each operation seen or what the SQL statement for that operation was. In order to get this information, one often needs to have a product such as IBM Tivoli Composite Application Monitor (ITCAM) installed.
However, there is another way. By setting up a WebSphere trace using the trace string of "*=info:RRA=all" and running your application's JDBC operations you can capture a great deal of information. (Note: depending on the frequency of the operations, this trace can incur some performance overhead, so you've been warned.)
By focusing on some key words in the trace we can collect details about when an operation started, when it finished, and the SQL statement(s) that ran during the operation, and we can compute the elapsed time for the operation. Here is a sample:
[3/19/10 10:10:03:726 CET] 00000035 WSJdbcPrepare > executeQuery Entry