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

On Jan 17, 12:15 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 17, 8:24 am, dweeb1..._at_gmail.com wrote:
>
> > Does anyone know what is the side effect of having a large open_cursor
> > variable.  We are doing imports into an oracle database which is heavy
> > on cursors.  We have found by setting a small number we keep on
> > getting the exceeds max number of cursors message.  Therefore, we have
> > set it much higher and wondering what possible side effect this may
> > cause the database as a whole, or is this some random number Oracle
> > places on databases?
>
> > -Thanas
>
> Depends on version!!!  Things changed in the middle of the 9.2 patch
> sets, including fixes for cursor memory leaks.
>
> Assuming you have a later version, you should likely be using
> session_cached_cursors in addition to open_cursors.
>
> There are many things that affect cursor usage, but since open_cursors
> is a maximum for a session, setting it high shouldn't have much effect
> these days.  Or as the docs put it, "Assuming that a session does not
> open the number of cursors specified by OPEN_CURSORS, there is no
> added overhead to setting this value higher than actually needed."
>
> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/m...
> explains that cursors are cached when 3 parse requests are made on a
> sql statement.  If you check what's inside of your dump file, you may
> notice a lot of insert statements, but they are all different.  So if
> you have a decently written app, session_cached_cursors should help,
> but probably won't help for imp (though if someone tested that, we'd
> all be grateful, I'm assuming imp is just mostly passing the SQL
> statements in the dump file through to the db and feeding the values
> into the cursor).
>
> BTW, open_cursor is a pl/sql function for opening a cursor,
> open_cursors is the init.ora that controls whether you get the maximum
> exceeded message.  Makes a difference when you search the docs.
>
> jg
> --
> @home.com is bogus.
> "If you see a bomb technician running, follow him." - USAF Ammo Troop

dweeb1, To add to Joel's fine reply the cost of increasing open_cursors is that more PGA memory is potentially used.

But I am not sure from your post if the errors were being encountered on the import or just by user sessions. Failure to close opened cursors in code can result in this error. I have pointed developers at their web application a couple of times when the developer told me that we needed to up the number and each time the lead developer found problems in the code. The developers would open cursors in a loop but not close them.

With connection pooling even low count usage code will eventually cause you a problem. This may not be your problem but you should be aware of it.

HTH -- Mark D Powell -- Received on Thu Jan 17 2008 - 12:34:00 CST