Re: Side Effect of having a large "Open Cursor"

On Jan 17, 10:34 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Jan 17, 12:15 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
>
> dweeb1, To add to Joel's fine reply the cost of increasing
> open_cursors is that more PGA memory is potentially used.

Flattery will get you everywhere :-)

Do you have a reference for this? I have no idea one way or the other, but I sure have seen more PGA used than expected at times. I had the impression/was assuming the cursor max was to limit memory allocation, but not actual memory allocation unless cursors were asked for (and it is documented that implicit cursors will use more than explicit). I certainly wouldn't be surprised if the details of this have changed over time. I see "Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor." in the docs, but that doesn't tell me if areas are created before that for the cursor. Unfortunately, I'm on 9 and don't have V$PROCESS_MEMORY to check. And of course, my open_cursors is set high because my app needs it anyways.

jg

--
@home.com is bogus.
http://www.smh.com.au/news/travel/powerhit-qf2-finally-touches-down/2008/01/09/1199554718579.html