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" <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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 oth
Re: open cursors problem
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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name o
RE: open cursors problem
Gavin - This topic came up a few weeks ago on this list. It that case the Oracle limit being reached was the number of processes. The answer was that "connection pooling is configured in weblogic properties". The three parameters to review are: initialCapacity, maxCapacity, capacityIncrement. Separately I am sending you the document where I captured the entire discussion. You can also find documents at http://www.weblogic.com. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, July 29, 2002 2: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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: open cursors problem
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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: open cursors problem
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
open cursors problem
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).