I have to admit that I wasn't thinking about replying to your comment when I sent this email. However, I think you are correct - there is an effect of extra items not being releasable from the shared pool when cursor_space_for_time is true. (From memory of one of Steve's seminars, it is the Heap 6 that ceases to be freeable). Whether this eliminates the creation and dropping of an x$kglpn entry I haven't yet checked.
Just as a quick test of what sorts of benefits could be achieved on latches by setting this parameter, I ran up a quick pl/sql loop and got the following results - (included in-line in case attachments get rejected, so the formatting is probably rubbish). (Version 9.2.0.3) ================================ Comments on cursor_space_for_time = true ======================================== Setting this parameter does change the latching in the library cache, but does not achieve total elimination. Significantly, more benefit comes from session_cached_cursors than cursor_space_for_time. Tested: ------- declare m_junk varchar2(20); begin for i in 1..100 loop execute immediate 'select ''abc'' from dual' into m_junk; end loop; end; / Environment changes: -------------------- session_cached_cursors = 0 / 100 cursor_space_for_time = true / false Counts taken from: ------------------ v$latch v$library_cache Latch cs4t = true cs4t = false ----- ---------- ------------ (Sess cache = 0) shared pool 379 492 library cache 925 1035 library cache pin 466 674 library cache pin alloca 440 440 (Sess cache != 0) shared pool 102 203 library cache 310 416 library cache pin 209 415 library cache pin alloca 0 0 Cache Gets Hits Pins Hits ----- ---- ---- ---- ---- cs4t=true --------- SQL Area 108 107 225 223 (sess cache = 0) SQL Area 1 1 101 101 (sess cache != 0) cs4t=false --------- SQL Area 108 107 333 331 (sess cache = 0) SQL Area 1 1 208 208 (sess cache != 0) ====================================== The execute immediate is deliberate to emulate a common coding paradigm, with explicit parse calls. You'll notice that the most significant fraction of the reduction in latch costs comes from the switch to using session cached cursors. But there is a further benefit from the cursor_space_for_time - and the most obvious change is in the 'library cache pin' - but it doesn't drop to zero. The point I was trying to make earlier, though, was about the general issues regarding latching in this area - even when you have a fantastically perfectly written application that only ever does 'parse once execute many' you can still get library cache (etc.) latch contention purely on extreme numbers and concurrency of execution. For those who haven't found it yet, there is a paper by Bjorn Ensig on OTN about (a.o) the cursor_space_for_time parameter and what it's doing to the shared memory. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 02, 2003 9:59 AM > Jonathan, > > I've understood that when cursor_space_for_time is true, then unpin is only > done when cursor is closed, thus there's no need for pinning/unpinning for > every execution of a cursor. This should reduce hits on library cache > latches since pinning is not done so often? > > Hermant, > > I've sometimes seen this parameter recommended when having library cache > latching issues in large Apps installations, I have not used it myself in > Apps though. > > Also note, that cursor_space_for_time requires 50-100% larger shared_pool > (and some more private SQL area in PGA, shared_pool or large_pool, depending > on configuration), since shared cursor's frames can't be aged out from > library cache until all corresponding cursors are closed (normally if > there's not enough free memory in shared pool when parsing a new statement, > some unpinned, but open cursors can be thrown out, but with > cursor_space_for_time they can't be). > > So, if you don't find any better cure and decide to use this parameter, you > should first increase your shared pool quite much to avoid ORA-4031 errors > and then start reducing in small amounts, based on v$librarycache, > v$rowcache, x$kghlu and shared pool/library cache latch wait statistics. > It's not good idea to leave shared pool too large, otherwise your memory > allocations from there (hard parses for example) will get slow (shared pool > latch (or latches in 9i) are kept too long when searching for > free/recreatable chunks). > > Tanel. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
