Basically, that parameter is a way to tell Oracle how many cursors to keep open per session. The higher you set it, the more memory each session will use, but if the sessions are likely to re-use the same SQL then you might see a performance increase by increasing this (if you have the spare memory, that is!)
thanx for the explanation, I just did a small test with session_cached_cursor, I set it to 4 and bounced the database. According to the paper on Metalink if session_cached_cursor is set then no soft and hard parse would happen if the cursor is cached.
I executed a simple query
select * from emp;
then checked parse_calls in v$sqlarea it says 1, then I executed again it says 2 then again it increased to 3 does this means that this select is not cached?
I then tested another pl/sql block
cursor crs is
select dname from dept;
fetch crs into name1;
exit when crs%notfound;
This happens the samething as above, everytime I execute it the parse_calls increases, I dont understand why since I am using a cached cursor (supposed to be)
Then I am reading about
CLOSE_CACHED_OPEN_CURSORS, it states that when a cursor is closed itīs not really closed, it is moved to a Least Recently Used (LRU) list of open cursors and left open. If this was the case then cursor is always cached no matter the value of session_cached_cursor no? Or if I set session_cached_cursor to 0 then CLOSE_CACHED_OPEN_CURSORS has no effect at all since no cursors are cached (i.e these two parameters are closely related)