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

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/memory.htm#i38400 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