RE: cursors
Date: Wed, 2 Dec 2015 15:46:24 +0000
Message-ID: <DM2PR12MB0314B189C230483A90B966F1F40E0_at_DM2PR12MB0314.namprd12.prod.outlook.com>
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<mailto: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<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<mailto: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-lReceived on Wed Dec 02 2015 - 16:46:24 CET