RE: session_cached_cursors parameter

It actually caches the whole instantiation object, not the just the library cache lock
(which serves as the pointer to

the shared cursor handle in the SGA). That means that it not only saves locating and
locking the library cache object
again, but it also saves extra instantiation calls as well. Also because the KGL lock
is retained, there is no
possibility of the shared cursor being aged out and thus not available on a subsequent
session cursor cache hit.

The memory cost can be non-trivial. I think it is about 240 bytes per populated slot
per session.

Although you are right that it is searched linearly, my experience suggests that the
CPU saving of session cursor cache
hits can exceed the CPU cost of unlatched linear searches with values much larger than
100.

The session_cached_cursors is just an array in the
UGA (session memory), which means it will be stored
in the SGA when using the multi-threaded server.

The function of the cache is to maintain pointers
into the shared pool so that on reuse of a closed
cursor, Oracle has the option of finding the cursor
very rapidly if it is still available, rather than going
through all the processing (and particularly latching)
needed to find if the 'new' SQL is sharable.

I believe the cost is actually a very small increment
in memory, plus a CPU cost for scanning the array,
which means that an array size over about 100 may
put you in the position of losing more CPU than you
would otherwise save.

Does it use PGA memory to cache parsed statements or
it uses SHARED_POOL memory for that.

If the case is the first one, imagine that cursor1 is
flushed out from the Shared Pool, and the session A
has it cached (in its PGA I assume), then, in order to
use it again, what must the session A do?
(it has to place the cursor into the shared pool again
and do a soft parse or that would not be
necessary...??)

thanks

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: steve.adams_at_ixora.com.au
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).