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

Thanks for the information. We are running Oracle 10.2g I believe the sub-version is. I was vey surprised that we had hit the upper limit when we did, but as you stated when dealing with insertions mostly, from an old system to a new system the "cached" cursors are useless. Of course, after the imports are done and the session is logged off the cursor count goes back down again. I do not imagine typical user usage will get anywhere near this number of cursors and therefore will decrease it after all the imports are done.

Again thank you.
-Thanas

> > 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
Received on Thu Jan 17 2008 - 12:46:46 CST