Re: cursors

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Wed, 2 Dec 2015 22:54:04 +0700
Message-ID: <CAP50yQ8pEfjuDq4k1LiHZqfokpg7tNeSvFzHr5sHY7zUxz_mkQ_at_mail.gmail.com>

That does match the _cursor_obsolete_threshold which had its default changed to 1024 (previously 100) between 11.2.0.3 and 11.2.0.4. But I'd specifically ask the vendor what they want you to set.

Do keep in mind that parameters prefixed with an underscore - such as _cursor_obsolete_threshold are so-called "hidden" parameters. You should check with Oracle support before changing any of these - particularly on a production database.

Stefan

On Wed, Dec 2, 2015 at 10:46 PM, Zelli, Brian <Brian.Zelli_at_roswellpark.org> wrote:

> Ok, the numbers seem to jive with the parameter_cursor_obsolete_threshold
> parameter in their notification.
>
> They talked about a parm that had the 100 default and then it was changed
> to 1024.
>
>
>
> Brian
>
>
>
>
>
> *From:* Mauro Pagano [mailto:mauro.pagano_at_gmail.com]
> *Sent:* Wednesday, December 02, 2015 10:34 AM
> *To:* Zelli, Brian
> *Cc:* MJ Mody; oracle-l (oracle-l_at_freelists.org)
> *Subject:* Re: cursors
>
>
>
> Whoever mentioned that is probably talking about
> "_cursor_obsolete_threshold".
>
> And the high number of child cursor (per parent cursor) is usually
> referred as "High Version Count".
>
>
>
> I suggest to focus on *why* so many cursors are generated
> (V$SQL_SHARED_CURSOR can help) rather than just obsolete the parent cursor
> when that happens.
>
>
>
> Btw the default value for parameter _cursor_obsolete_threshold has been
> bumped between 11.2.0.3 (100) and 11.2.0.4 (1024) so the recommendation to
> lower it "just because" doesn't match with Oracle approach (there are
> reasons why more than 100 could be expected, i.e. each user has its own
> copy of the tables)
>
>
>
> On Wed, Dec 2, 2015 at 10:21 AM, Zelli, Brian <Brian.Zelli_at_roswellpark.org>
> wrote:
>
> They are saying to change it from 2000 to 100. Everything I google tells
> me to leave it higher. They say having high counts of child cursors (>
> 1000) have been known to cause performance degradation and ORA_4031
> errors. An Oracle init parameter in 11gR1 can mitigate the associated
> impact by limiting the number of SQL cursors to 100. Does this sound right?
>
>
>
>
>
> Brian
>
>
>
>
>
> *From:* MJ Mody [mailto:emjay.mody_at_gmail.com]
> *Sent:* Wednesday, December 02, 2015 10:15 AM
> *To:* Zelli, Brian
> *Subject:* Re: cursors
>
>
>
> open_cursors is the one you should change.
>
>
>
> best
>
> mj
>
>
>
> On Dec 2, 2015, at 9:06 AM, Zelli, Brian <Brian.Zelli_at_RoswellPark.org
> <Brian.Zelli_at_roswellpark.org>> wrote:
>
>
>
> Ok, I received a notification from our vendor to change the limit of SQL
> Cursors.
>
> When I do a show parameters, I see open_cursors and
> session_cached_cursors.
>
> Is it either one of these?
>
>
>
>
>
> Brian
>
>
>
>
>
>
> This email message may contain legally privileged and/or confidential
> information. If you are not the intended recipient(s), or the employee or
> agent responsible for the delivery of this message to the intended
> recipient(s), you are hereby notified that any disclosure, copying,
> distribution, or use of this email message is prohibited. If you have
> received this message in error, please notify the sender immediately by
> e-mail and delete this email message from your computer. Thank you.
>
>
>
>
> This email message may contain legally privileged and/or confidential
> information. If you are not the intended recipient(s), or the employee or
> agent responsible for the delivery of this message to the intended
> recipient(s), you are hereby notified that any disclosure, copying,
> distribution, or use of this email message is prohibited. If you have
> received this message in error, please notify the sender immediately by
> e-mail and delete this email message from your computer. Thank you.
>
>
>
> This email message may contain legally privileged and/or confidential
> information. If you are not the intended recipient(s), or the employee or
> agent responsible for the delivery of this message to the intended
> recipient(s), you are hereby notified that any disclosure, copying,
> distribution, or use of this email message is prohibited. If you have
> received this message in error, please notify the sender immediately by
> e-mail and delete this email message from your computer. Thank you.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 02 2015 - 16:54:04 CET