Re: Holdable cursors in Oracle?

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