Strange open cursors for "select null from dual" on Oracle 9i and 10g
Michael Pfeifer
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
Joel Garry
9.
Please state init.ora parameters with cursor in them for each version,
ie select name, value from v$parameter where name like '%cursor%';
Also, if you have OEM, you can drill down from a sesssion to look at
cursors. Perhaps your app server is doing many things.
jg
--
@home.com is bogus.
"Thanksgiving turkey, Christmas turkey, Valentines's Day beaver, Easter
rabbit." - Get Fuzzy
Michael Pfeifer
> Please state exact versions, cursor handling has been changing through
> 9.
>
> Please state init.ora parameters with cursor in them for each version,
> ie select name, value from v$parameter where name like '%cursor%';
>
> Also, if you have OEM, you can drill down from a sesssion to look at
> cursors. Perhaps your app server is doing many things.
>
> jg
Thanks for the reply.
We have tested with
- Oracle Standard Edition 9.2.0.1, using ODBC driver 9.2.0.1 and
9.2.0.4
- Oracle Enterprise Edition 9.2.0.1, using ODBC driver 9.2.0.4 and
9.2.0.65
- Oracle Enterprise Edition 9.2.0.6, using ODBC driver 9.2.0.65
The settings resulting from "select name, value from v$parameter where
name like '%cursor%'" are:
cursor_space_for_time FALSE
session_cached_cursors 0
cursor_sharing EXACT
open_cursors 300
Note that open_cursors is likely to be much higher in one of our
productive systems. This is just my test system.
What do you mean by "Also, if you have OEM, you can drill down from a
sesssion to look at cursors."?
I can only see the number of cumulative open cursors and the number of
currently open cursors. Things that I can see also when using Toad or
when selecting from v$open_cursor. Or do I miss something?
And what do you mean by "Perhaps your app server is doing many
things."
Sure, our app server is doing a lot of things. And it has been
behaving well on Oracle 8i for many years. But on Oracle 9i and 10g we
are seeing all the trouble with the open cursors.
Regards,
Michael
yon...@yahoo.com
other part with semicolon)? This is explained in ODBC Help, "Format of
the Connection String (Programming)". CSR means "close CurSoR enabled".
Yong Huang
Joel Garry
One way is to right click on the session in "Top Sessions," hover over
Drilldown, and click on "open cursors for this session." Right click
on each for more interesting info. There are probably other ways, and
perhaps this requires some option to be purchased (can't recall offhand
and don't have time to investigate, been too long since I installed
what I'm using).
Select distinct s.sql_text from v$SQL s , v$open_cursor o where sid =
:1 and s.hash_value=o.hash_value and s.address = o.address according to
the help.
>
> And what do you mean by "Perhaps your app server is doing many
> things."
> Sure, our app server is doing a lot of things. And it has been
> behaving well on Oracle 8i for many years. But on Oracle 9i and 10g
we
> are seeing all the trouble with the open cursors.
Well, now that we see your patching levels, maybe you need to
investigate session_cached_cursors on your 9206 version. And I'm not
sure it is a good idea to be running earlier versions, see the patch
bugfix list. Also, see if the compatibility option makes any
difference, setting it to an 8i version. I know most people think 300
should be enough cursors, but I've seen some apps that need much more.
jg
--
@home.com is bogus. The English woman was visiting Paris for the first
time. She told a friend: "I've been here for two weeks already and I
still haven't been to the Louvre." "It must be the water," her friend
said.
Joel Garry
are running out of cursors?
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20050406/news_1b6peregrine.html
Michael Pfeifer
Well we do not use a connection string, we use an ODBC datasource. The
datasource configuration allows to set an "Enable Closing Cursors"
checkbox, which is exactly the same as using CRT in the connection
string.
Thanks anyway.
Michael Pfeifer
> Oh, one more thing, what exactly do you see that makes you think you
> are running out of cursors?
>
> jg
After using the system for a while we get an error message on
executing a SQL statement. The error message tells that we are running
out of cursors. That simple ;-)))
yon...@yahoo.com
follow up with Metalink thread 170710.999? I wish we could contact the
guy that said "that is exactly what we were looking for".
Yong Huang
Joel Garry
>executing a SQL statement. The error message tells that we are running
>out of cursors. That simple ;-)))
You must tell us the exact oracle error number. ORA-1037? ORA-1000?
ORA-35564? Is the error being returned to you or is there something in
the alert log? We are neither endodontists nor telepathists here.
jg
--
@home.com is bogus.
Maybe the phone work manana:
http://www.signonsandiego.com/uniontrib/20050408/news_1b8cingular.html
Michael Pfeifer
I followed the thread and found nothing that would make me think that
the two are not just different paths to the same goal. As far as I
understood the guy just wanted the connection string option that is
equivalent to the "Enable Closing Cursor" checkbox offered for a ODBC
data source. And he got it (CSR=T). Therefore he says "that is exactly
Michael Pfeifer
> >After using the system for a while we get an error message on
> >executing a SQL statement. The error message tells that we are running
>
> >out of cursors. That simple ;-)))
>
> You must tell us the exact oracle error number. ORA-1037? ORA-1000?
> ORA-35564? Is the error being returned to you or is there something in
> the alert log? We are neither endodontists nor telepathists here.
>
> jg
Sorry for answering that late (I have been assigned to an even more
pressing problem) and not being more precise. I always thought that
there was only 1 error message regarding the number of open cursors -
the classic ORA-1000. It is returned to our application which in turn
shows it to the user. We did not get it from the alert log or similar.
Michael Pfeifer
I tried to set the compatible parameter of the 9.2 installation to
8.1.7. But this is nothing that you can do just in a second. I tried
half a day to overcome a lot of problems (locally managed tablespaces,
various incompatibilities, ...). I finally gave up after trying to
solve error ORA-00402, respectively ORA-00405 which prevent the
database from opening. I searched the web for ORA-00405 and how to
overcome the "PDML ITL invariant" issue. Though I followed the
instructions I found there ("downgrading a database"), I could not
open the db. Then I decided that this procedure is nothing that our
customers are likely to accept and dropped this path.
With respect to session_cached_cursors I must admit that I do not see
the direct correlation to my problem. As far as I understand
session_cached_cursors is a feature which may improve performance by
caching parsed statements, but nothing that would cause a bottleneck
in the sense that it would prevent a statement from executing at all
if the threshold is reached. Anyway I checked our environment. We do
not have special settings in this case which means that the Oracle
default values (for this parameter: 0) apply