Re: Ref Cursor Overhead

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D05039F.AFFE7DDD_at_exesolutions.com>...
> Greg A wrote:
>
> > What type of overhead is involved in using ref cursors vs. implicit
> > cursors? I have developers that insist on using ref cursors for
> > output variables instead of individual output variables even when
> > returning result sets that will always contain a single record. They
> > argue that they don't want to concern themselves with data types or
> > the number of columns being returned. They only want to deal with
> > record sets.
> >
> > Besides the fact that this type of programming seems sloppy to me
> > (just an opinion), I have argued that there is additional overhead
> > involved in using ref cursors and that they should be reserved for
> > where they are truly needed like when returning multi-record result
> > sets or when using native dynamic SQL.
> >
> > Is this an argument worth pursuing? Is there enough additional
> > overhead involved in utilizing ref cursors that I should enforce that
> > they be used sparingly?
> >
> > I have read in a few places that ref cursors require a good deal of
> > overhead vs. implicit cursors, but I'd like to confirm the fact.
> >
> > Any input that anyone could provide would be greatly appreciated.
> >
> > Thanks.
>
> Based on a series of emails I received from Tom Kyte, and my experience
> following his advice, I would say that the cost is high.
>
> I will quote Tom:
> "Don't use the ref cursor unless yopu have to pass it to some subroutine
> or what not. If that open for is in a loop -- it could be very costly and
> won't rer 100% of its ugly head until you do it in a heavy multi-user
> environment."
>
> Following that advice I rebuilt an application at AT&T Wireless and
> solved some major performance problems.
>
> Daniel Morgan

Daniel,

Thanks a lot for the feedback. Ironically, I had quoted Tom Kyte to my developers when I was explaining to them the need to avoid ref cursors. My quote came from an Oracle newsgroup thread in which Tom explained that ref cursors should only be used when they are truly needed because "regular" or "implicit" cursors are more efficient. I too have a great amount of respect for Tom. I was hoping to get some feedback from other sources as well in order to further support my argument. Your point about rebuilding an application at AT&T wireless (I am assuming that you removed unnecessary use of ref cursors) is very good and will help to further prove my point. Again, thank you very much for the information.

If anybody else has any similar stories or can provide further support for the avoidance of overusing ref cursors, your feedback would be greatly appreciated.

Regards,
Greg Received on Tue Jun 11 2002 - 08:05:11 CDT