Performance and scalability: Oracle JDBC OCI Driver Memory Leak Issue

  1. Oracle JDBC OCI Driver Memory Leak Issue (1 messages)

    Hello all,
    After working through a memory-leak problem with our Oracle OCI JDBC Driver (type 2) I thought I'd share the knowledge in hopes it might benefit other Consultants/Developers.

    We're using WebLogic v6.1 on Win2000 and Solaris. We're connecting to an Oracle 8.1.6 database. We've written an web application that uses Oracle's OCI JDBC driver for version 8.1.6. We're calling stored procedures using CallableStatements.

    In production when 3 users would use the application for about 10 hours the WebLogic java process would get an OutOfMemoryError and crash. Max heap size was set to 256Mb, which seemed sufficient for the application. Something was eating up memory.

    We downloaded a trial of Mercury LoadTest and I wrote an HTTPUnit script to hit the application with a simulated load of 16 users. In 6 minutes with 256Mb Heap size for the WebLogic process we could reproduce the problem and cause the JVM to crash. Good news, we could reproduce the problem.

    Next I used OptimizeIt to monitor the WebLogic java process. Using the Memory profiler while running the load scripts we could see that Oracle's DBItem class (can't remember the full package name) was creating instances and never releasing them. Other Oracle classes too were getting created and never released, but DBItem was by far the biggest offender (over 120,000 instances were created in 5 minutes!). We ensured that all our ResultSets, CallableStatements and Connections were being closed and set to null, but the problem still persisted. Oracle's JDBC object instances were never being garbage collected.

    We first switched to Oracle's 8.1.7 Thin driver (type 4) and repeated the profile and load test. The thin driver doesn't run out of memory and crash the JVM (good), but eventually we get socket write errors and max cursors exceeded (bad). So the JVM doesn't crash but database connectivity eventually drops off...the lesser of two evils.

    We found our solution with WebLogic's jDriver. It's an Oracle OCI driver (type 2) that comes with WebLogic. When profiled and subjected to heavy load it performed wonderfully! As we monitored the JVM heap with WebLogic's admin console we could see garbage collection working as it should. Compare this to Oracle's drivers which continued to use more memory until they ran out and crashed the JVM.

    I should mention that shortly after this happened we searched and found some Oracle TARs on this very issue. We're using CallableStatements connecting to stored procedures and registering out parameters (cursors). This is important. Apparently there's something with using CallableStatements that continue to hold onto resources well after they should have been released. Since they don't get released, garbage collection can never reclaim memory and eventually the process runs out of memory.

    In conclusion, if you're using Oracle's JDBC drivers with CallableStatements beware. Under a relatively low load the problem will most likely be masked. However, start adding more users and you might run into a memory leak problem!

  2. Hi Ryan,

    I guess this is related to this issue. I remember we were also getting the same kind of memory leak problems around six month back. We were using WL4.5 with win2000, solaris & oracle oci driver for 8.1.7. We were also using CallableStatements connecting to stored procedures and registering out parameters (cursors). We figured out something about this. Sometimes Oracle doesn't free up the resourses even though you are making sure to close connection, callable st & resultsets. For this you should call clearParameter() method after getting the resultset back. This method is defined in the PreparedStatement interface. CallableStatements extends this interface. This releases all the resources used by the current parameter values. After changing our code we never got this problem again.