Oracle Open Cursor Problem

[GoogleCodeExporter]

What version of the product are you using? On what operating system?
Activeweb 1.9

Please provide any additional information below.

We have recently gone live with activeweb and activejdbc an a fantasy football 
site for a big newspaper with a lot of traffic.

The Oracle DBAs are complaining to me that the application has an unacceptable 
number of open cursors.

We are investigating, but the number of open cursors is so high that I am 
wondering if we are doing something radically wrong.

We are using the dbconnection filter with:
addGlobalFilters(new DBConnectionFilter());

We were assuming that the above filter would take care of closing and opening 
connections.

Now, I have a question: will the above filter close the connection if the 
controller action throws an exception?

Or do we need to catch all exceptions in the controller actions in order to 
guarantee that the above filter will close and clean up?

(we have quite a few controller action which throw exceptions such as 
JSONException, SQLException)

Original issue reported on code.google.com by jayse...@gmail.com on 24 Aug 2013 at 1:25

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hi. congrats on going live with the site. What is the URL? Curious. 

As far as your question, adding a DBCnnectionFilter globally is not the best 
thing to do, since it will open a connection to DB even if you are executing a 
controller that does not use a connection. We usually use a different form:

add(new DBConnectionFilter()).to(OneController.class, TwoController.class....);
this way you will be opening connections for those calls that actually use it, 

Original comment by i...@expresspigeon.com on 24 Aug 2013 at 6:30

[GoogleCodeExporter]

to answer your question, yes DBCOnnectionFilter will close a connection even if 
you have an exception, see here:
https://github.com/javalite/activeweb/blob/master/activeweb/src/main/java/org/ja
valite/activeweb/controller_filters/DBConnectionFilter.java

Original comment by i...@expresspigeon.com on 24 Aug 2013 at 6:32

[GoogleCodeExporter]

Hi the URL is http://www.gazzetta.it/magic/pub/gp/magic3/

They have 150k teams playing.

We have now added the connection filter to specific classes.

I have checked that the filter closes the connection even if an exception
such as null pointer is thrown.

A colleague has checked the activeweb source code for result sets and or
statements not being closed, but he has not found any.

The Oracle DBAs may be wrong, but they are still saying our webapp keeps
too many open cursors.

Original comment by jayse...@gmail.com on 5 Sep 2013 at 6:48

[GoogleCodeExporter]

Hi Igor,

the client's Oracle DBAs are still hassling me about the open cursors on 
Oracle: they say our activeWeb/activeJDBC application is not closing cursors.

Given that we are sure that Connection.close() is guaranteed by the 
DBConnectionFilter, the following questions remain:

Are the result sets and the prepared statements explicitly closed by the 
ActiveJDBC framework?

If not, are you relying on the Connection.close() to do the cleanup of the 
Oracle open cursors?

Bear in mind that we are using a connection pool, and therefore the physical 
oracle connections are not really being closed. The close() in a pooled 
situation just releases the physical connection so that it can be used by the 
next request that needs the connection.

Kind Regards,

Jeremy

Original comment by jayse...@gmail.com on 19 Sep 2013 at 9:22

[GoogleCodeExporter]

sorry for delay, transitioning got Github, and having two places for issues 
currently. I do not remember if we are relying on db connection close method, 
but the first implementation of ActiveJDBC was used to build a massive 
processing system for an insurance company. It was based on Oracle enterprise, 
and we have not experienced any issues you are describing

Original comment by i...@expresspigeon.com on 27 Sep 2013 at 2:15

[GoogleCodeExporter]

also, a code base to explore what is closed is not in ActiveWebm it is in 
ActiveJDBC: https://github.com/javalite/activejdbc
tx

Original comment by i...@expresspigeon.com on 27 Sep 2013 at 2:18

[GoogleCodeExporter]

Hi Igor, a while has passed, but I should update you on the progress. The DBAs 
were right, activejdbc was not closing prepared statements on inserts. This 
happens when using the save() method to insert a row (looking at the source 
code, the save() method uses a statement cache, but this also means that 
statements do not get closed).

However, the insert() method does not use a statement cache, and it DOES close 
the oracle prepared statement.

So our solution was simply to replace the save() methods with insert() methods.

The DBAs have confirmed that this solved the issue.

I realize that this issue should be under activeJDBC rather that activeWEB. Do 
you need me to open an issue on the JDBC section?

Original comment by jayse...@gmail.com on 17 Dec 2013 at 2:54

[GoogleCodeExporter]

yes, please do. We migrated code to Github, please file here:
https://github.com/javalite/activejdbc/issues
Also, if you think you can contribute, feel free to send in a pull request 

thanks

Original comment by i...@expresspigeon.com on 17 Dec 2013 at 6:05