Tracing and Improving SQL requests with p6spy

David GEORGE

1st October 2004

Contents

  1. Introduction
  2. P6SPY
    Installation under Weblogic
    Installation under Websphere 4.0
  3. Analysing a request with Oracle 8.0
    Explain Plan
  4. Other Advice
  5. Appendix

Introduction

Many things affect the performance of a J2EE application. For example the network speed, the architecture of the application, whether the Lan Segment is shared etc.  A simple rule is that the more an element in an application is shared the more it is susceptible to be a bottleneck with important repercussions on general application performance. This implies that performance improvement should start at the back end of the application and progess towards the client. In a J2EE application the performance of EJBs and the RDBMS are more important than individual JSPs and servlets.

An application server such as Websphere or Weblogic has many tuning parameters. For example, the number of connections in the pool, the size of the SQL prepared statement cache, the servlet cache, the number of threads in each Java Virtual Machine (JVM) or if the Object Request Broker (ORB) passes parameters by reference when the client (servlet) and the server (EJB) execute within the same JVM. Changing each of these factors can improve the application performance.

Most of the problems are caused by application code and access to the database. Some examples: a badly written sQL request, a poor database scheme (over or under normalised), no or incorrect indexing on table, a 1:1 mapping between the database tables and the object scheme (a typical error with Container Managed Persistence (CMP) in J2EE 1.1), an over use of finders in Entity Beans rather than replacing with a custom SQL request in a session bean. An additional problem is that the SQL generated by an application server is hidden from the developer.

It is also worth noting that performance is not regular, especially when the volume of data increases on a daily basis.  Tuning production databases is one of the key functions of a good database administrator.

To examine the SQL it is necessary either to have access to the original source code (decompilers can help) or to use a trace utility. This last technique is often the only option to examine SQL generated directly by the application server. However in the last case performance improvements are limited to modifying the database schema, changing objects or mappings or replacing the application server.

Application servers often provide their own trace tools. In Weblogic it is simply necessary to edit the properties file or use the administration tool in later versions and add the following lines:

weblogic.jdbc.enableLogFile=true
weblogic.jdbc.logFileName=jdbc.log

The server needs permission to write to the file: jdbc.log, to visualise the contents of the file in real time, use the Unix command

$ tail –f jdbc.log

If you are running on Windows you will need to install the Redhat Cygwin toolkit if you want to have access to the excellent Unix command set.

Next Section: p6spy Installation