Everything works fine, except if I execute about 40-50 rapid executions of the JSP page, I run out of cursors.

I have rs.close, stmt.close, and connection.close() commands at the end of each SQL call, BUT I see the cursor count climb with each execution.

The cursor count does go down with time, - I imagine the cursor times out. Also I notice that once the jsp page executes, I will not see the db connection close until a few minutes later, even though I have issued conn.close() explictedly (?) !

Is the connection pool smaller than the cursor limit? Perhaps try reducing the pool size?

Perhaps try doing a garbage collect or runFinalization after closing the connection. Do the connections close immediately now? For performance you don't want to do this if not necessary, but it may be better than falling over?

If you are using a connection pool the connection.close does not actually close the connection, it just returns it back to the pool.
First thing to do is to make sure you have a try and a finally and that you close all your db resources in the finally block, maybe you could put up the code in your jsp and what appserver are you using to do database pooling
try {
} catch (Exception e) {
} finally {
// close all db resources here
}

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Hi keithedward,
conn.clolse() doesn't actualy close the database connection. It marks it as "free" and sends it back to the pool. When another instance wants a connection it gives connection from the pool. There are parameters for the connection pooling, which controls the inactive timeout. This means if the connections stays idle for a specified time, it is automaticly close (physicaly).
Also check your code if it closes the connection in case of exception. As back@devx suggest, modify your code so you can have closing of all db-resource at the finaly stage.
hope this helps

When I run the jsp page, and then look at the Oracle cursor count with:

select * form v$sysstat where name='opened cursors current'

I see the cursor count jump, and linger long after the page has finished loading. Eventually the count drops, BUT I would like to know WHY the cursor count does not drop immediately after giving the rs.close and stmt.close ???

DO NOT DECLARE DB RESOURCES TO BE PRIVATE!!
In a JSP/Servlet environment it is multi-threaded, if the public void makeConnection method is called by two threads one after the other you'd get two connections but only hava handle to one of them because you myConn is set twice once by the first thread and then again by the second thread.
You need to make your app thread safe, you will always have issues if you run this code and if you run this for extended periods of time the oracle server will reset a connection which you do not have a handle to, this will caues a socket reset by peer exception and crash your JVM which in turn means that you're Orion appserver will stop.
It's a pretty common problem I've come across best thing to do is to have a DBManager to handle all your access to db resources, it will connect run a query get the results populate a java object and return that java object. The DBManager can implement a singleton pattern and synchronize the methods or have static methods with all db resources being variable local to the method rather than private variables.

Just remember two rules when accessing the database
1. Never have the variables with scope other than local unless you're entire class is thread safe
2. ALWAYS have a finally block and close all your db resources in there

The same object is shared by different threads, therefore your variable which has class scope because it's declared private will keep changing. Have it as only local scope and make the method thread safe by synchronizing it and you'll be fine.
Static is even worse because every instance of that class will be sharing it, you're problem will become significantly worse if you make the variable static.
With private variable an instance of each private variable exist for each instance of a class, with static variables only once instance of the variable exists for all instances of the class.

Introduction
This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…

Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…