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 -> Re: Strange open cursors for "select null from dual" on Oracle 9i and 10g

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

From: Joel Garry <joel-garry_at_home.com>
Date: 7 Apr 2005 14:30:24 -0700
Message-ID: <1112909424.212908.129880@f14g2000cwb.googlegroups.com>

Michael Pfeifer wrote:
> "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?

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.
Received on Thu Apr 07 2005 - 16:30:24 CDT

Original text of this message

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