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" <tanel.poder.003_at_mail.ee>
> Date: 2003/12/02 Tue PM 12:39:26 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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" <ORACLE-L_at_fatcity.com>
> 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" <ORACLE-L_at_fatcity.com>
> > > 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" <ORACLE-L_at_fatcity.com>
> > > > 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: jonathan_at_jlcomp.demon.co.uk
> > > >
> > > > 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: 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.net
> > > --
> > > Author: Tanel Poder
> > > INET: tanel.poder.003_at_mail.ee
> > >
> > > 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: 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).
> > >
> >
> > 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: mladen_at_wangtrading.com
> >
> > 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: 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.net
> --
> Author: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> 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: 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.net
--
Author: <ryan_oracle_at_cox.net
INET: ryan_oracle_at_cox.net
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: 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).
Received on Tue Dec 02 2003 - 12:04:33 CST
