Re: max open cursors exceeded

From: elain he <elainhe_at_hotmail.com>
Date: Wed, 22 Oct 2003 09:09:26 -0800
Message-ID: <F001.005D3FB4.20031022090926@fatcity.com>

Mladen,
cursor_sharing=exact
cursor_space_for_time=false
open_cursors=400
session_cached_cursors=0

Thanks.

elain

>From: Mladen Gogala <mladen_at_wangtrading.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: max open cursors exceeded
>Date: Mon, 20 Oct 2003 09:34:25 -0800
>
>Do you have CURSOR_SPACE_FOR_TIME set to true?
>On 10/20/2003 12:39:25 PM, elain he wrote:
>>Hi,
>>I'm trying to troubleshoot ora-1000 (max cursors exceeded) for an
>>application. When I ran the following query on the SID(192),
>>
>>select hash_value ,count(*)
>>from v$open_cursor
>>where sid=192
>>group by hash_Value
>>having count(*) >1;
>>
>>HASH_VALUE COUNT(*)
>>---------- ----------
>>670480087 5
>>563605149 3
>>1016653255 10
>>2005317811 3
>>
>>hash value: 1016653255 is a simple SQL statement- select sysdate from
>>dual;
>>
>>the results returned hash_values with counts as high as 10. I understand
>>that even if cursors are closed, they are still cached in server memory
>>and will still show up in v$open_cursor. I do not understand why there
>>are duplicate sql statements in v$open_cursor for the SID. Does that mean
>>that Oracle is not reusing the cursor for whatever reasons and thus opens
>>new ones? If that is true, what could be the reasons that 'select sysdate
>>from dual' could not be reused?
>>
>>Thanks.
>>
>>elain
>>
>>_________________________________________________________________
>>Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage.
>>http://join.msn.com/?PAGE=features/es
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: elain he
>> INET: elainhe_at_hotmail.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).
>Mladen Gogala
>Oracle DBA
>
>
>
>Note:
>This message is for the named person's use only. It may contain
>confidential, proprietary or legally privileged information. No
>confidentiality or privilege is waived or lost by any mistransmission. If
>you receive this message in error, please immediately delete it and all
>copies of it from your system, destroy any hard copies of it and notify the
>sender. You must not, directly or indirectly, use, disclose, distribute,
>print, or copy any part of this message if you are not the intended
>recipient. Wang Trading LLC and any of its subsidiaries each reserve the
>right to monitor all e-mail communications through its networks.
>Any views expressed in this message are those of the individual sender,
>except where the message states otherwise and the sender is authorized to
>state them to be the views of any such entity.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).

---

Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: elain he
  INET: elainhe_at_hotmail.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 Wed Oct 22 2003 - 12:09:26 CDT