cursor_space_for_time To Be Deprecated

If you haven’t seen the Meatlink note 565424.1 in the news yet, cursor_space_for_time parameter will be deprecated in Oracle 10.2.0.5 and 11.1.0.7.

That’s kind of good news, I hope this will eventually reduce the number of expert DBAs who set this parameter to true whenever they see any kind of shared pool / library cache latch contention.

On the other hand, spin_count was made an undocumented parameter long time ago, but is still heavily abused worldwide so I wouldn’t be surprised if the same happens to future _cursor_space_for_time…

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Oracle and tagged , , , . Bookmark the permalink.

8 Responses to cursor_space_for_time To Be Deprecated

  1. Noons says:

    The note seems to indicate the condition for not using this parameter is only valid after 10.2.0.5 and 11.1.0.7, which are both not yet released.

    So if it is being used now, what is the problem other than the noted PL/SQL case with dynamic sql and binds?

  2. tanelp says:

    Hi Noons,

    The problem I’ve seen too many times is that a DBA has read from somewhere that cursor_space_for_time helps as it pins cursors into memory (in addition to reading that _spin_count always helps when you have CPU starvation!). Thus they start tweaking those parameters while they should look into why their CPUs are so busy etc. So, such DBAs can spend days tweaking parameters and bouncing instances, until business decides to double the number of CPUs. But a quick look into that top CPUu consuming execution plan (heavy nested loop instead of hash join) would have reduced the CPU usage down to normal levels and no need to upgrade CPUs at all!

    cursor_space_for_time is not evil, but it’s too often abused, together with _spin_count.

  3. Noons says:

    Agreed 100% on the sillyness of using parameters just because “someone” once saw them work, in a galaxy far, far away.

    Tuning the SQL is almost always a better way of approaching performance problems.

    But the thing to be also aware of is that most places nowadays run apps where the SQL is not available to be individually spot-tuned.

    Eg: any “canned” application, including Oracle’s own. Such as:
    Peoplesoft, Siebel, JDE, Oracle CRM/ERP. And so on. Not just Oracle either…

    So while we can certainly detect and isolate the “nasty nested loop”, there is often preciously nothing that can be done to affect the SQL itself: it’s locked away in an executable.

    Or else, under some form of silly “agreement” that essentially reads: “you touchy, no warranty!”

    It’s then that even things like single statement profiling can still get us into hot water 1 year later, when someone patches the app and all heck breaks lose.

    Hence why sometimes it’s handy to have these “global behaviour change” parameters: basically, nothing else will work in a fashion that is easy to maintain and control…

    And don’t get me started on Peoplesoft pay calcs!
    :)

  4. tanelp says:

    Yep, agreed too, sometimes I’ve had to get really creative to work around a problem in some fundamentally wrong application – without changing the app itself. Views in local schemas, materialized views, dbms_advanced_rewrite, etc etc. Oracle 11g has introduced even more features for working around bad design in crappy applications…

    Nevertheless, cursor_space_for_time and _spin_count are not the parameters which help you to solve such problems effectively.

    Anyway, I was ranting just because some recent experience with someone fiddling (again) with those parameters.

  5. Marc Torres says:

    Totally agree that cursor_space_for_time and _spin_count do not fix the underlying problem. Whatever that is.

    I’d say they tend to have no impact on performance. Sometimes though, the impact can be negative (seen that with cursor_space_for_time) and should be rolled back.

    Having said that, I would not demonise a time-starved production DBA who gives this a try just in case… :-)

  6. binzhang says:

    cursor_space_for_time help a lot in our 10g production.
    After set it to TRUE, Library cache latch wait time and Library cache pin wait time decrease a lot and active session spkie during peak time also disappeared.

    sure that it requires more shared pool size and large pool size.

  7. tanelp says:

    Yes, and most importantly it requires properly designed cursor management in the application! And apparently you had that.

    It will not help in cases where library cache latch contention is just a symptom of excessive parsing or just CPU overload.

    Marc, sorry I did’t notice your earlier post… I still think its right to fix the problem, not the symptom, but I also appreciate that in corporations there’s often pressure to “do *something*” instead of “lets think about it and fix it for good”

    Thanks for the comments.

  8. Ray L says:

    Please correct me if I am wrong.

    1. Obsolete parameters are those, setting in init file has no impact and not considered by Oracle.

    2. Deprecicated parameters are those, it impacts if sets in init file but can be ignored if present?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>