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: Michael Pfeifer <muchinger_at_web.de>
Date: 26 Apr 2005 08:23:37 -0700
Message-ID: <91b29f39.0504260723.486876d@posting.google.com>


"Joel Garry" <joel-garry_at_home.com> wrote in message news:<1112909424.212908.129880_at_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

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 Received on Tue Apr 26 2005 - 10:23:37 CDT

Original text of this message

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