-
1. Re: Is there any solution for Oracle open cursor problem?
dsundstrom Jul 17, 2002 5:11 PM (in response to vvarimo)I am not an Oracle guy, so can you tell me what causes this problem. I can say that the JBossCMP code opens a statement, executes, loads the results and closes the statement all within one method, but my guess the problem is not leaks.
-
2. Re: Is there any solution for Oracle open cursor problem?
vvarimo Jul 18, 2002 3:06 AM (in response to vvarimo)I have a management interface for my CMP 2.0 entity beans which is used by test cases. Those test cases are run over network. I have a test case for each create, update, get, remove and relation method. Transaction settings are set to 'required'.
When I run the tests (there are about 110 test cases which contain 20-30 tests) the amount of open cursors in database starts increasing fast.
Does JBoss open a statement for each transaction and if so are those statements closed after transaction completes?
-Vesa- -
3. Re: Is there any solution for Oracle open cursor problem?
vvarimo Jul 18, 2002 3:06 AM (in response to vvarimo)I have a management interface for my CMP 2.0 entity beans which is used by test cases. Those test cases are run over network. I have a test case for each create, update, get, remove and relation method. Transaction settings are set to 'required'.
When I run the tests (there are about 110 test cases which contain 20-30 tests) the amount of open cursors in database starts increasing fast.
Does JBoss open a statement for each transaction and if so are those statements closed after transaction completes?
-Vesa- -
4. Re: Is there any solution for Oracle open cursor problem?
dsundstrom Jul 20, 2002 1:16 PM (in response to vvarimo)Transaction creation depends on how you have configured the transaction attributes in the ejb-jar.xml. By default you get a transaction for each create, update, get, remove. I suggest you use a wrapping session facade or manage the transactions your self with a UserTransaction.
For each statement JBossCMP closes the statement, resultset and connection immediately after reading the data.
Are you actually getting a cursor leak? -
5. Re: Is there any solution for Oracle open cursor problem?
vvarimo Jul 22, 2002 1:59 AM (in response to vvarimo)I'm not sure... If JBoss really closes statement after transaction, there should not be so much open cursors in Oracle.
-
6. Re: Is there any solution for Oracle open cursor problem?
dsundstrom Jul 23, 2002 8:44 PM (in response to vvarimo)Try writing a simple benchmark and see if it leaks from one run to the next. You may be able to track this down. Also are you using XA? XA has ver different preformance charistics then a standard JDBC connection.
-
7. Re: Is there any solution for Oracle open cursor problem?
vvarimo Jul 30, 2002 1:44 AM (in response to vvarimo)It seems that I'm using oracle.jdbc.driver.OracleDriver which uses OracleConnection that is implementation of java.sql.Connection. I could find javax.sql. package and even OracleXAConnection from Oracle jdbc drivers, but I couldn't figure out from where jdbc driver is configured to use XA connection instead of java.sql.Connection.
I'll try again with XA drivers and write that benchmarking test. I'll report results here.
Thanx,
-Vesa- -
8. Re: Is there any solution for Oracle open cursor problem?
sarge Jul 31, 2002 5:57 PM (in response to vvarimo)About 18 months ago I ran into a similar problem with running out of cursors in Oracle in a stand-alone Java application. I tried closing each statement directly after use and throwing away the reference, but nothing worked. I never got resolution from Oracle, but it has been quite some time.
-
9. Re: Is there any solution for Oracle open cursor problem?
apost Oct 22, 2002 12:37 PM (in response to vvarimo)Anything yet? Anybody? A workaround? An appropriate sacrifice to the computer gods?
Seriously, this problem is a showstopper. We're trying to load several thousand entities using the container, and I get through maybe 30 before I run out of cursors. I've looked through the JBoss code and it does indeed look as if the result set and prepared statement are being closed shortly after they're being created (in a finally block, so it can't be missed).
My boss is threatening me with BEA. Help! :) -
10. Re: Is there any solution for Oracle open cursor problem?
scoy Oct 22, 2002 11:51 PM (in response to vvarimo)Are you using the absolute latest JDBC driver from Oracle?
It is easy to identify because it contains a manifest with the following content (together with a lot of other stuff):
Oracle JDBC Driver version - 9.0.2.0.0
We're working with thousands of entities and Oracle without this kind of problem. -
11. Re: Is there any solution for Oracle open cursor problem?
bruce_b Dec 19, 2002 6:33 AM (in response to vvarimo)We had a similar issue, don't know if this will help...
We traced our code back to the connection initialisation, and found a statement to set the date format. The comment next to the code said "Don't know why we need to do this really, but anyway...."
I guess it's in some standards book somewhere - the guy who wrote the code wouldn't have done it on purpose.
This statement grabbed a cursor and was not closed in the finally block.
Once the connection was set up, 'control' returned to the main classes, and any reference to the date format statement was lost. Obviously, it could no longer be shut.
Btw, we tried upgrading the jdbc drivers to the latest, but the problem was code in the end.....
As I say, I don't know if this will help - I hope so, and maybe it might prompt you to look somewhere similar and hopefully find your issue.
Regards,
Bruce -
12. Re: Is there any solution for Oracle open cursor problem?
lorensrosen Dec 19, 2002 1:25 PM (in response to vvarimo)It may be worthwhile tracing this from the other end, by
getting Oracle to tell you who opened these cursors and
what query they are executing. A capable DBA should be
able to do this. See for example the script on this page:
http://www.think-forward.com/sql/opencur.htm