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<mailto: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<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<mailto: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.
