Debugging Oracle PreparedStatement Leaks
Introduction
Over the past several weeks I debugged several PreparedStatement
leaks in an application from a third party vendor. This post summarizes several techniques I found effective in tracking down the piece of code that exhibits those bugs. I will also show several approaches how you can monitor or avoid PreparedStatement
leaks.
What is a PreparedStatement
leak, how does it happen and why is it bad? A PreparedStatement
leak happens when you forget to call #close()
on a PreparedStatement
this prevents
- the
PreparedStatement
from releasing its associated Java resources (which can be significant) - the
PreparedStatement
from being garbge collected - the database from releasing any associated resources
The simplest possible PreparedStatement
leak looks something like this:
As leaked PreparedStatement
are never garbage collected and can have quite large Java resources associated with them (at least with the 11g driver) PreparedStatement
leaks tend to result in an application crash caused by a OutOfMemoryError
. Other possibilities are failing connections due to too many open cursors for a database session.
Post Mortem
The final manifestion of a PreparedStatement
leak is an application crash caused by a OutOfMemoryError
. Running your JVM with -XX:+HeapDumpOnOutOfMemoryError
ensures that if a OutOfMemoryError
occurs you have a heap dump which can be analyzed. If no heap dump is available fixing a java.lang.OutOfMemoryError
becomes much more difficult. The first thing I do when analyzing a heap dump is load it into Eclipse MAT and verify it is indeed a PreparedStatement
leak. This can be done looking at the number of T4CPreparedStatement
instances in the heap. If there are a couple of thousand then we are looking at a prepared statement leak. Now that we have established that we are indeed dealign with a PreparedStatement
leak the next step is to extract the SQL used to create these statements. From the SQL it is often easy to search the code base for the place that exhibits the bug. When using the ojdbc driver the SQL of all the PreparedStatement
instances in the heap can be found with the following OQL query:
after exporting the result to a CSV and importing it into SQLite Manager the result can be postprocessed, trailing characters can be removed and the queries can be grouped and counted.
Monitoring
It is important to note that the previously described technique only works once you have a PreparedStatement
leak in production in the worst case crashing you application. It is best to find PreparedStatement
leaks as realy as possible, ideally during development but at least before they crash your application.
While developing there are various static anaysis tools that you can run that can detect PreparedStatement
leaks. Findbugs is a popular one but many IDEs like Eclipse also offer build in checks for leaks. It is important to note that none of these tools are perfect and eventually a PreparedStatement
leak my slip into production unnoticed. Also for third party applications you usually do not have the source code available.
At runtime there are several options how you can monitor your application for PreparedStatement
leak. Many APM products offer built in checks. But there are several other options you can employ.
A simple option is to count the number of PreparedStatement
instances in the heap. You can do this using jcmd
.
jcmd <pid> GC.class_histogram | grep PreparedStatement | wc -l
beware that while this command runs all Java code in the JVM is stopped. An application with more than a thousand PreparedStatement
objects in the heap likely has a PreparedStatement
leak.
Oracle also allows you to monitor the open cursors per sessions. If a database session has more than one hundred cursors open you likely have a PreparedStatement
leak. Your SysDBA knows more.
And finally the Tomcat connection pool (jdbc-pool) allows you to tack and close the open PreparedStatement
per request. This can be a band aid to keep your application running despite having PreparedStatement
leaks.
Avoiding PreparedStatement Leaks
There are several ways how you can design your code so that it never leaks PreparedStatement
s.
- You can use Java 7 try-with-resources. Java 7 is the oldest version of Java supported by Oracle as of the time writing this article, there is no reason to not bet at least using Java 7.
- You can use an ORM or a different database access framework like jOOQ, MyBatis or QueryDSL. These abstract JDBC away and manage JDBC resources for you.
- You can use a utility class like Spring JdbcTemplate or Apache Commons DbUtils. These do not abstract JDBC away but still manage JDBC resources for you. Note that
JdbcTemplate
does not require a Spring application context, so you are not forced to use Spring if you want to useJdbcTemplate
.