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).
