RE: cursors

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 2 Dec 2015 23:35:00 +0100 (CET)
Message-ID: <73983960.1016242.1449095700933.JavaMail.open-xchange_at_app03.ox.hosteurope.de>



Hi Brian,
unfortunately you have not mentioned your exact Oracle version, but be aware that "_cursor_obsolete_threshold" was an enhancement request implemented with 11.2.0.3 - otherwise (e.g. 11.2.0.2.x afaik) you have to use event 106001 (and "_cursor_features_enabled") to limit version count.

This is just a slightly add to what Mauro says. You should really troubleshoot the root cause for that high amount of child cursors or even better if this is really impacting you.

You can get some insights into the reason for limiting the amount of child cursor by reading Christian's blog post: - https://antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/ - https://antognini.ch/2012/10/does-the-parse-time-increase-linearly-with-the-number-of-child-cursors/

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK  

> "Zelli, Brian" <Brian.Zelli_at_RoswellPark.org> hat am 2. Dezember 2015 um 16:46 geschrieben:
>
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 02 2015 - 23:35:00 CET

Original text of this message