Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Strange open cursors for "select null from dual" on Oracle 9i and 10g

Strange open cursors for "select null from dual" on Oracle 9i and 10g

From: Michael Pfeifer <muchinger_at_web.de>
Date: 4 Apr 2005 23:22:13 -0700
Message-ID: <91b29f39.0504042222.4103c18b@posting.google.com>


We have an application server running against Oracle 8i, 9i and 10g databases on Windows using ODBC. With Oracle 8i it runs without issues, but with Oracle 9i and 10g we have seen a strange behavior with regard to open cursors.
We have a test case which we run for each of the 3 databases and each test case we execute once with the "Enable Closing Cursors" (ECC) checkbox of the ODBC datasource checked and the other time without checking ECC. At the end of each test run we look at the open cursors. These are the results of our tests (values are approx., there might be +/- 1 open cursor for single test cases, but I want to show the big picture):

Oracle 8i
With ECC checked: 14 open cursors, all basing on statements <> "select null from dual"
Without ECC checked: 68 open cursors, all basing on statements <>
"select null from dual"

Oracle 9i
With ECC checked: 68 open cursors, 54 of them basing on the statement
"select null from dual"

Without ECC checked: 68 open cursors, all basing on statements <>
"select null from dual"

Oracle 10g
With ECC checked: 68 open cursors, 54 of them basing on the statement
"select null from dual"

Without ECC checked: 68 open cursors, all basing on statements <>
"select null from dual"

The problem is that with Oracle 9i and 10g we run out of open cursors (even if increasing the number of open cursors), while on Oracle 8i we do not. The code that we run is always the same - so this is kink of mystery to me.

To me it seems that the 54 open cursors which make up the difference when running against a ODBC sources with respectively without ECC checkbox checked are not "freed" correctly on Oracle 9i and 10g. They remain in the system as open cursors basing on "select null from dual" though our application server does not issue such a statement.

I hoped to be able to solve the problem using different ODBC drivers (e. g. 9204, 92065) and Oracle server (9206, 10103) versions, but in vain ...

Please help. Thanks in advance,
Michael Received on Tue Apr 05 2005 - 01:22:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US