what causes memory fragmentation errors? should oracle be able to go to the LRU and start kicking stuff out of memory if there isnt enough space? > > From: "Tanel Poder" <[EMAIL PROTECTED]> > Date: 2003/12/02 Tue PM 12:39:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute > > Mladen, > > I don't think it's SMON who is coalescing free memory extents. I'm not > entirely sure here, but I think if any server process explicitly frees a > freeable chunk, then the 16-byte header of immediate next chunk is checked, > if this is also free both chunks are coalesced and header of next chunk is > checked and so on. When no more adjacent free chunks are found, shared pool > freelists are updated. This is called forward coalescing (not to be confused > with on-disk segment extent forward coalescing), Ixora also mentions a bit > about them. > > This all is done by the server process who is freeing the chunk, not SMON > (SMONs sleep interval is too long for this kind of critical operation > anyway). > > Also, when a process tries to allocate memory from shared pool and there are > no sufficiently large free chunks left, then the process goes to shared pool > LRU list to find unpinned recreatable chunks and uses callback through the > kernel stack to find the "owner" of the chunk and free it appropriately. > When freeing chunk for new allocation like that, here we might also have > forward coalescing going on (adjacent free space is coalesced before > allocated to new process). > > Actually, I'm not sure whether this "callback" is real callback up the > kernel stack or is a separate context estabilished for it like Steve Adams > describes for data and transaction layer in the beginning of his book. > Estabilishing a separate call context for such a low level operation seems > quite expensive. If anyone knows about this, please let us know ;) > > Mladen, another way for circumventing excessive memory usage in shared pool, > in addition to cursor_sharing, is to tell TFDs to use bind variables > appropriately ;) > > Tanel. > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, December 02, 2003 5:49 PM > > > > That was my understanding, too. The problem with unpinning only at > > the specific close is that smon cannot free shared pool memory belonging > > to the cursor if the cursor is pinned, so the shared pool usage > skyrockets. > > The only way to circumvent the problem is to set CURSOR_SHARING to FORCE. > > That is also fraught with danger, but what the heck, we are the DBAs, we > want > > to live dangerously. > > > > On 12/02/2003 04:59:33 AM, Tanel Poder wrote: > > > 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. > > > > > > ----- Original Message ----- > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Sunday, November 30, 2003 9:34 PM > > > > > > > > > > > > > > You still have to hit the library cache to execute > > > > a statement as it needs to be pinned in share mode, > > > > and unpinned when you finish with it. Library cache > > > > latch waits can be a symptom of excessive executions. > > > > > > > > Have you checked the library cache latch children > > > > to see if the load is evenly balanced, or whether there > > > > is a single library cache latch that is suffering most of > > > > the sleeps. > > > > > > > > Good news for 9.2 - v$sql, and a couple of others > > > > include the library cache child latch number, so you > > > > can see which objects are protected by the hot latch > > > > without having to use Steve's algorithm. > > > > > > > > > > > > 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: Sunday, November 30, 2003 1:29 PM > > > > > > > > > > > > What's the value for your cursor_space_for_time parameter? > > > > > > > > Tanel. > > > > > > > > ----- Original Message ----- > > > > From: Hemant K Chitale > > > > To: Multiple recipients of list ORACLE-L > > > > Sent: Sunday, November 30, 2003 8:54 AM > > > > Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute > > > > > > > > > > > > > > > > I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On > occassion > > > I > > > > still see > > > > very high LIBRARY CACHE LATCH contention and am considering upping > the > > > > value again. > > > > Currently, I set it at the Instance level. Since I am running > Oracle > > > > Apps, I have suggested > > > > to the application team to put a custom ALTER SESSION trigger into > the > > > > specific first > > > > responsibility form for users who do navigate between forms a lot > and > > > > where we see > > > > high contention. > > > > Running Steve Adams's query, I get > > > > SQL> @Session_Cursor_Cache.sql > > > > > > > > PARAMETER VALUE USAGE > > > > ----------------------------- ----- ----- > > > > session_cached_cursors 400 50% > > > > open_cursors 1024 36% > > > > > > > > > > > > CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES > > > > ----------------- ----------- ----------- > > > > 35.10% 63.09% 1.81% > > > > > > > > > > > > MAX_CACHEABLE_CURSORS > > > > --------------------- > > > > 5227 > > > > > > > > > > > > > > > > -- > > > > 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). > > > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Tanel Poder > > > 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). > > > > > > > Mladen Gogala > > Oracle DBA > > > > > > > > Note: > > This message is for the named person's use only. It may contain > confidential, proprietary or legally privileged information. No > confidentiality or privilege is waived or lost by any mistransmission. If > you receive this message in error, please immediately delete it and all > copies of it from your system, destroy any hard copies of it and notify the > sender. You must not, directly or indirectly, use, disclose, distribute, > print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the > right to monitor all e-mail communications through its networks. > > Any views expressed in this message are those of the individual sender, > except where the message states otherwise and the sender is authorized to > state them to be the views of any such entity. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Mladen Gogala > > 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). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > 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). >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
