Re: open cursors problem

The issue of open cursors and connection pooling (using the weblogic properties parms you mention) are completely different. Many cursors can be opened within a single connection from the pool.

Too many open cursors is a case of a poorly written app, at least in my experience with weblogic/java apps. So I'd agree that it's a developer coding issue and not a connection pooling issue. And also agree with Rachel that setting open_cursors=1000 is reasonable, then it's a developer education issue.

Gavin D'Mello wrote:
>
> Dennis William sent me a mail containing the discussions on this same topic
> a couple of weeks back. That discussion layed the blame a bit on the
> database config parameters in the weblogic properties file like
> initialCapacity, maxCapacity, capacityIncrement
>
> Is this really the case ? or just developer error of not closing the
> prepared statements ?
>
> Gavin
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, July 29, 2002 6:43 PM
>
> > I think the problem is that that's the value in the init.ora file for
> > his database, NOT the limit of Oracle itself.
> >
> > According to the docs, the maximum number of open cursors you can allow
> > in 8.1.7 AND 9.2 is 1 to 4294967295 (4 GB -1). And that limit is for
> > each individual user, not cumulative total for the database.
> >
> > so the "crash" may be that the parameter is set to 750 in the
> > initialization file for that database, in which case, if you hit it
> > then you need to raise that number in the init.ora and bounce the
> > database.
> >
> > If that's not the problem, then I would seriously review my code. Why
> > ANY individual user would have that many open cursors in a single
> > session is something to check. Usually means the app is written not to
> > clean up after itself. There IS a limit to how much memory you can use
> > before the machine hangs itself with constant swapping.
> >
> > I had one like that in 7.3.4, the programmers never bothered to
> > explicitly close the cursors when done, they let it default to when the
> > session was ended, the end users would click open new sessions without
> > closing down old ones. We finally set open_cursors to 1000 and let it
> > fail, to teach the end-users to close windows. Why didn't the
> > programmers rewrite the code? Damagement there decided that the
> > programmers knew better than the DBA about how things should work in
> > conjunction with the database. And, of course, fixing bad code doesn't
> > show as "progress" on status reports.
> >
> >
> >
> > --- "Toepke, Kevin M" <ktoepke_at_trilegiant.com> wrote:
> > > WHAT! A cap of 750 open cursors in the database? Where did you hear
> > > that?
> > >
> > > -----Original Message-----
> > > Sent: Monday, July 29, 2002 3:38 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi everyone,
> > > I am running EJB under weblogic 5.1 jdbc 2.0,
> > > and
> > > when I last checked the database the number of open cursors was 564.
> > > I know
> > > 8.1.7 has a cap of 750 open cursors after which the db will crash.
> > > Since
> > > this is the first time I have faced this problem I would be grateful
> > > if
> > > anyone could point out what could be the possible areas that could
> > > cause
> > > this problem ( other than the logical one of not closing cursors once
> > > they
> > > have been opened and used !).
> > >
> > > any help here would be appreciated.
> > >
> > > thank you,
> > >
> > > Gavin
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Gavin D'Mello
> > > INET: gavin_d_at_tis.co.in
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing
> > > Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Toepke, Kevin M
> > > INET: ktoepke_at_trilegiant.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing
> > > Lists
> > > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Health - Feel better, live better
> > http://health.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gavin D'Mello
> INET: gavin_d_at_tis.co.in
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: lvordos_at_qwest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).