Is the Problem Really in the Oracle Database?

When performance issues arise in a web application, you should first validate that the problem is actually in the database before you start to tune the server code. Starting from the outermost round trip, you should add timers to code that will run in the client and on the application server. However, you should be warned that discussing this topic in any depth is difficult because the actual procedure depends heavily on the specific technology being used.

In the client code, as long as you are using a mainstream browser, the browser debug mode will capture all of the available information to indicate how long execution is taking at the client as well as the time taken for round trips between the client and the application server.

In the application server, the same sort of timing markers can be placed in the middle tier code as in the database code. Unfortunately, this can become a very complicated problem since there are many different architectures that can be used to code applications. Many of these architectures are themselves made up of large architectural components such as business rules engines, web services, model layer architectures, and others. Relevant for the purposes of this book are what calls are made to the database for any user interface operation and how many times those calls are made. These are the timings that need to be determined.

It is essential to capture this information. A routine may be called hundreds of times, requiring a small but significant portion of time (e.g., 1/100 of a second) for each call. This can add up to a sizeable impact on the application.

It is always important to determine how the time that a routine takes is distributed across all portions of the application cycle. Spending resources to tune database code makes little sense if that code only comprises 5 percent of the total execution time.

Each architecture will have its own way of handling direct calls to PL/SQL program units in the middle tier. The following is an example of code that includes timing markers for a simple JDBC call:

This code will indicate the amount of time required to execute the routine in the database plus the time required to transmit and return the data between the application server and the database.