Hi Jonathan, 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. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -----Original Message----- Sent: Saturday, 6 October 2001 4:57 To: Multiple recipients of list ORACLE-L Since no-one's answered this, I'll propose my theory, although I'm not sure it's right. 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. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 03 October 2001 22:03 Does anyboby know how this parameter really works? 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
