Re: Holdable cursors in Oracle?

Billy Verreynne wrote:

> Sybrand Bakker wrote:
>
> >>What is the reasoning, functionally and technically, behind such a
> >>concept?
> > to avoid parsing
>
> A single hard parse to open a cursor is negligble. Should other sessions
> want to open a similar cursor (assuming everyone plays nice and use bind
> variables), these would be soft parses.
>
> >>How are transactions from different users/sessions handled on this cursor
> >>and data integrity guaranteed?
> >>
> > A cursor exists *within* a session
>
> Exactly. How then does the logic work behind a global "holdable" cursor,
> accessible from multiple sessions?
>
> What is the reasoning for wanting this? The original poster also mentioned
> that it is a "feature" of certain db systems. It sounds to me more like a
> hack-turned-into-a-feature.
>
> >>Issues wrt to resource usage are moot IMO. I fail to see how these can
> >>dictate a fundemental concept such as holdable cursors.
> >
> > They aren't. In fact in Pro*C, you can close a cursor and you can
> > release it. If you don't release it, and only reevaluate it, you can
> > save a parse.
>
> You're talking about something different Sybrand - refreshing a cursor if I
> understand you correctly. This has nothing to do (from my understanding)
> with a shareable cursor.
>
> What I understand is that the original poster is after global persistant
> session-sharable cursors. I can not see logic behind wanting this. The only
> reason I can think of, is to reduce the cursor overheards. Instead of 10
> sessions, each having 1 cursors, you have 10 sessions sharing the same
> cursor.
>
> However, in that case, there are resource and performance overheads in
> making that global cursor sharable among 10 sessions. For example, each
> session will require its own cursor state data, such as the current cursor
> row pointer. Code is needed to ensure the integrity and consistency of this
> shareable cursor wrt transactions on it.
>
> All these negates the little space savings of not having 10 separate
> cursors. After all, is the basic concept of a cursor to *ISOLATE* sessions'
> data access from one another?
>
> Besides, sessions sharing a single cursor sounds too much like junkies
> sharing a needle. Bloody dangerous, with a lot of potential for data
> diseases.
>
> --
> Billy

The shareable cursor if I recall correctly is a standard feature in Teradata and I have reason to believe Oracle may be using them but not explicitly: Not as a developer defined cursor type.

Daniel Morgan Received on Wed Mar 19 2003 - 10:31:45 CST