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

From: Michael Pfeifer <muchinger_at_web.de>
Date: 6 Apr 2005 00:06:01 -0700
Message-ID: <91b29f39.0504052306.1acaa8bd@posting.google.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:<1112740899.328577.183480_at_f14g2000cwb.googlegroups.com>...
> 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 Received on Wed Apr 06 2005 - 02:06:01 CDT