RE: session_cached_cursors parameter

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 <ORACLE-L_at_fatcity.com> 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: 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).