Re: Ref Cursor Overhead

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 Received on Mon Jun 10 2002 - 14:53:07 CDT