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@xxxxxxxxxxxxxxx>
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@xxxxxxxxx]
*Sent:* Wednesday, December 02, 2015 10:34 AM
*To:* Zelli, Brian
*Cc:* MJ Mody; oracle-l (oracle-l@xxxxxxxxxxxxx)
*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@xxxxxxxxxxxxxxx>
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@xxxxxxxxx]
*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@xxxxxxxxxxxxxxx
<Brian.Zelli@xxxxxxxxxxxxxxx>> 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.
