Hi All, Could any one help me on this as its very urgent and the application has been removed from the production because of this problem.
We went through the code just to make sure if there are any connections/resultsets/statements are left openend. But we have taken care to close all connections, resultsets and statements and its been done in the standard format.
public boolean closeConAndStatement(Connection con, PreparedStatement prepareStmt, ResultSet resultset) { try { if (resultset != null) resultset.close();
if (prepareStmt != null) prepareStmt.close();
if (con != null) con.close();
resultset = null; con = null; prepareStmt = null;
return true; } But still i dont know why the hell this problem is happening.
Server details: 1. Oracle database server 2. WAS 4.0.4 Connection pool settings in WAS: min=1 and max=30 open_cursors=300 in init.ora file
The application runs fine for some days and then starts giving this problem. The open_coount has reached to 150.
Also it would be great if explain the difference b/n cursor and connection.
Thanks in advance, Prakash [ May 17, 2006: Message edited by: Bear Bibeault ]
You usually want to put the close code inside a finally clause, that way if something else fails, it will still run the close code. It looks like you have the close code in the try part.
yes its been closed in the finally block only.....all that part is very fine...please let me know if there are any settings in oracle level or WAS level can help us to solve this problem...
Prakash, In my opinion, you're going to have to debug it via the Oracle "V$" views, like "V$OPEN_CURSOR". Please refer to the "Oracle Database Reference" (part of the Oracle documentation) for details on the "V$" views. For your information, the Oracle documentation is available from:
Hi Avi, Thanks for your help. I have executed my this query many times and also we can see some of our our queries holding the cursor. But we are closing the connections/resultsets/statements properly after executing the query. Please let me know if there is way, i can find out that eiter application server or the oracle server are not releasing the cursors properly.
Also the client says, there are more than 100 apps using the same Instance and they are not facing the problem. Is it possible for other applications to get the cursor when it says, ORA-01000: maximum open cursors exceeded for the other application which is using the same instance.
Prakash, Are you saying that you have already looked at the "V$" views, and that you can see that there are open cursors (which should be closed)? If so, then it seems that you are not closing all the "ResultSet"s, "Statement"s and "Connection"s in your code.
I don't use WAS, so I can't help you, but I imagine there may be some way to run it in "debug" mode. I work with OC4J and you can run it in debug mode by setting some "System" properties when starting up OC4J. Perhaps there is something similar in WAS. Do you know? Have you tried?
Regarding Oracle, you can set tracing on in the database. I'm only going from memory, but you can do something like:
Please check the Oracle documentation for the correct syntax (and more details).
You may also be able to incorporate P6 Spy into your application. That may also help you to track down your problem[s].
As far as I know, the "maximum cursors" is a per-session limit. In other words, each session can have 300 open cursors. Again more details are available in the Oracle documentation. However, since you are using WAS, you are probably using a connection pool, so you may have several users using the same database session, so if you have a lot of concurrent users all using the same session, I guess you could reach your open cursors limit quite easily.
Good Luck, Avi.
Prakash Chicka
Greenhorn
Joined: Mar 09, 2005
Posts: 23
posted
0
Hi Avi, Thanks lot for your reply. The problem is happening in the production server (may be tomorrow they will take our app out from production) and they have executed the query and sent me the output. According to that, our application has opened 160 cursors. I have been telling them that, there are many ways they can tune the database to overcome this problem. Since I am not a DBA, I dont know where exactly we can tune it in the databse.Also they are not ready to trace it and give us the inputs. Strange thing they are saying that "There are more than 100 Apps. using this Instance and they are not facing such problem". I just cant beleive this as cursors will be shared across the applications.
It would be great if anybody can give me the exact solution in the oracle level or WAS level, so that I can ask them to do the same.
Hello I im facing the same problem. I use websphere4.0.
But now I can give some research result even I have not solved the problem yet.
1.In websphere4.0, I use connectin pool. If I use statement, then there is not any problem. If I use preparedStatement, the I encounted the same problem. In the v$open_cousor, there are many records even I have closed every preparedStatement.
2.In tomcat4.0-..., I do not use connection pool, then there is not any problem. Even I do not close the statement and preparedstatement.
luo hao
Greenhorn
Joined: Jun 13, 2005
Posts: 2
posted
0
J2EE container would do something for preparedStatement, that is setting the preparedStatement Pool. Websphere4.0 can do that, and the initial preparedStatement pool size is 100. So even in application the preparedStatement has been closed, but that is only indicate that it has been returned to the preparedStatement pool. If we set the preparedStatement pool size to 0, then in the V$open_cousor table, no cousor would be found.
//-- CLOSE DE PREPAREDSTATEMENT BEFORE REUSE // //-- THIS CLOSE THE OPEN CURSORS FOR THIS STMT //
try{stmt.close();}catch (Exception ex){}
//-- NEW PREPAREDSTATEMENT // stmt = con.prepareStatement(sql.toString());
rs = stmt.executeQuery();
// AND CLOSE FOR NEW REUSE. try{stmt.close(); }catch (Exception ex){ } ................................
Good Luck, Rabelo.
Originally posted by luo hao: Hello I im facing the same problem. I use websphere4.0.
But now I can give some research result even I have not solved the problem yet.
1.In websphere4.0, I use connectin pool. If I use statement, then there is not any problem. If I use preparedStatement, the I encounted the same problem. In the v$open_cousor, there are many records even I have closed every preparedStatement.
2.In tomcat4.0-..., I do not use connection pool, then there is not any problem. Even I do not close the statement and preparedstatement.
Mark, Thanks for your help but still we are not able to figure out the root cause. we cant expreriment each time as the application is in production and we are not able to reproduce the same in our test environment.
We had a similar problem with Oracle 8i. One of our friendly 3rd party supplied us a set of classes (jar file) which we were using in our application. We had to decompile their code to find out the problem. They didn't even have a single preparestatement.close() in their code!. Though this problem was identified and fixed by the 3rdy party a long time back, Our friendly release team had applied the wrong version of the jar file in the production env! and hence we couldn't reproduce the problem in our test envs!
My suggestion would be to check the software/application verion differences between live and test envs!
All EJB app servers use pool DataSource driver to connect to the databse. .ie "oracle.jdbc.pool.OracleConnectionPoolDataSource" This is the point of trouble.
I had the same problem.
I get ride of pool datasource driver and went with old way of doing it. ie. simple jdbc driver from sun. "DriverManager.getConnection(dbUrl, _databaseUserName, _databaseUserPassword);"
And this fix the problem. Since I used simple jdbc driver from sun, I never had any problem so far.
If you need a pool of connection, write your own pool using sun jdbc connection.
TIP: create the number of connection you want to keep in pool, then add them in vector. Write little manager to handle the connection sharing.
This works. In most of the case, to be honest to will not really need even a pool.
If you need help about writing connection pool from sun jdbc connection driver drop me email. supritchaudhary@yahoo.com -Suprit
I am also facing error "Max cursor exceeded" in production environment.
The same application code was working well in WAS4, however, after migration to WAS5.1, the error started coming up every alternate day.
We have made it sure that all RS, STMT and connection objects ae closed in finally block.
To take preventive meausure, we are bouncing the server every night to reduce the cursor count to zero.
Details JDBC Provider class - oracle.jdbc.pool.OracleConnectionPoolDataSource oracle driver - classes12.zip oracle driver version - 9.0.2.0.0 URL = jdbcracle:thin:@wpshXXXX.win.XXXXXX.com:1521:wXX5 Minimum pool size = 10 Maximum pool size = 100 Connection time out = 180 s Idle time out = 1800 s Orphan time out = 1800 s Statement cache size = 1000 (for wepod instance) Disable Autoconnection cleanup = unchecked OPEN_CURSOR Parameter - 400
Any pointer towards solution is appreaciated!
Can anybody let me know how do I decide the value for parameter "Statement cache size" @ WAS side and OPEN_CURSOR @ DB side
We're pleased to have you here with us in the Oracle forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.
In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.
This problem mainly happens when you are using connection pooling because when you close connection that connection go back to the connection pool and all cursor associated with that connection never get closed as the connection to database is still open.
So one alternative is to decrease the idle connection time of connections in pool, so may whenever connection sits idle in connection for say 10 sec , connection to database will get closed and new connection created to put in pool.
0
racle:thin:@wpshXXXX.win.XXXXXX.com:1521:wXX5