| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: maximum open cursors exceeded
RE: maximum open cursors exceededThe problem was that open_cursor caused an array of cursors to be allocated specified in that parameter. If you set this to 1000 then all sessions would allocate this array (MTS could have problems with this ;-)). Since 7.3 there is a change that the array of cursors is now segmented. Each segment will can 64 cursors. That doesn't mean that you would allocate the real cursors, just the pointers to these cursors. When the cursor is opened by the user it will allocate the memory.
Anjo.
Tom Kyte mentions that cursors are allocated 64 at a time .. BTW oracle says the upper limit is 4^32-1 so go crazy ...
Raj
-----Original Message-----
From: Goulet, Dick [mailto:DGoulet_at_vicr.com]
Sent: Tuesday, April 15, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: maximum open cursors exceeded
Murat,
Have you tried increasing the shared pool size? I remember reading a MetaLink note about max open cursors that said that it may return that same error message when in reality it could not acquire sufficient shared pool space to handle the request.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Tuesday, April 15, 2003 6:19 AM
To: Multiple recipients of list ORACLE-L
Hi,
again the same problem. Following results are from the while I do not get any errors. Sometimes, I get ORA-01000: maximum open cursors exceeded. Every query made by the application leaves an open cursor.
What's the problem?
Thanks in advance...
Murat
P.S : I'm not the developer of the application. I can only change the sql statements and have changed every statement to use bind variables.
open_cursors = 300
The value is : 2566
The value is : 11
MAXIMUM COUNT OPENED_ONCE OPEN OPENS HITS HIT_RATIO 300 0 0 0 0 0 1
OPENS HITS HIT_RATIO 736760 736760 1
* SELECT USER_NAME, A.SID, NVL(OSUSER,'UNKNOWN'), NVL(MACHINE,'*'),
NVL(PROGRAM, 'UNKNOWN'), COUNT(B.SID) FROM V$SESSION A, SYS.V_$OPEN_CURSOR B WHERE A.SADDR = B.SADDR GROUP BY USER_NAME, A.SID, OSUSER, MACHINE, PROGRAM;
USER_NAME SID NVL(OSUSER,'UNKNOWN') NVL(MACHINE,'*') NVL(PROGRAM,'UNKNOWN') COUNT(B.SID)
myUSER 10 oracle jdbcclient JDBC-1.0-Client 274 myUSER 28 oracle jdbcclient JDBC-1.0-Client 276 myUSER 46 oracle jdbcclient JDBC-1.0-Client 246 myUSER 68 oracle jdbcclient JDBC-1.0-Client 243 myUSER 82 oracle jdbcclient JDBC-1.0-Client 273 myUSER 85 oracle jdbcclient JDBC-1.0-Client 266 myUSER 88 oracle jdbcclient JDBC-1.0-Client 240 myUSER 99 oracle jdbcclient JDBC-1.0-Client 245 myUSER 109 oracle jdbcclient JDBC-1.0-Client 271 myUSER 113 oracle jdbcclient JDBC-1.0-Client 237)
* select * from v$sesstat where statistic#=3 AND Sid in (
SELECT A.SID FROM V$SESSION A, SYS.V_$OPEN_CURSOR B WHERE A.SADDR = B.SADDR and user_name = 'MYUSER' GROUP BY USER_NAME, A.SID, OSUSER, MACHINE, PROGRAM
SID STATISTIC# VALUE
10 3 274 28 3 276 46 3 247 68 3 243 72 3 1 82 3 273 85 3 266 88 3 241 99 3 245 109 3 271 113 3 237
* select v.sid,
v.serial#, v.username, v.status, count(*) from v$session v, v$open_cursor c
SID SERIAL# USERNAME STATUS COUNT(*) 10 1172 MYUSER INACTIVE 274 28 5620 MYUSER INACTIVE 276 46 20004 MYUSER INACTIVE 247 68 212 MYUSER INACTIVE 243 72 33546 MYUSER INACTIVE 1 82 18511 MYUSER INACTIVE 274 85 50896 MYUSER INACTIVE 266 88 18614 MYUSER INACTIVE 241 99 1132 MYUSER INACTIVE 245 109 5387 MYUSER INACTIVE 271 113 11210 MYUSER INACTIVE 237
This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system.Telsim Mobil Telekomunikasyon Hizmetleri A.S. does not accept legal responsibility for the contents of this message.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MURAT BALKAS
INET: murat.balkas_at_o2.net.tr
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Goulet, Dick
INET: DGoulet_at_vicr.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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: anjo_at_oraperf.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 Apr 15 2003 - 11:28:12 CDT
 |
 |