Oracle-L: OPEN CURSORS Question

From: Viktor <stant_98_at_yahoo.com>
Date: Tue, 21 Jan 2003 09:55:30 -0800
Message-ID: <F001.00536420.20030121095530@fatcity.com>

Hello,

I'm trying to find out what's causing this problem in one of the databases we have: Oracle 8.05/ Sun Solaris. Customers are using a front-end app where they access database via Forms built modules and run a number of reports. The problem is that occasionally they get errors, each time slightly different ones -- I'm still waiting to hear the exact error message as of this morning, but I'd like to find out whether this may be as simple as rasing the value of OPEN_CURSORS in init.ora. It's currently set to 350 (small size db). I'd raise it to 750 or 1000, but I'd like to see for myself what's causing the errors (may be increased usage, other factors...).

Anyways, part of this select's output is this:

SQLWKS> SELECT s.sid, s.value, t.name

2> FROM v$sesstat s, v$statname t

3> WHERE s.statistic# = t.statistic#

4> AND s.sid = 105;

SID VALUE NAME

---------- ----------------------------------------------------------------

105 33882 opened cursors cumulative

105 343 opened cursors current

I realize that there may be a number of implicit cursors that are used in the application which increase the likeness of this type of error happening. As you see, for this user "opened current cursors" is 343, but OPEN_CURSORS is set to 350. Besides looking into SQL as possible target for tuning, could it be that OPEN_CUROSRS is set too low for a front-end OLTP small sized DB?

Thank you very much!

Regards,

Viktor

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Viktor
INET: stant_98_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Tue Jan 21 2003 - 11:55:30 CST