Re: (long and boring) "SQL AREA" and "LIBARARY CACHE" size?

An expert is the one who fully understands all of the important relationships between different parts of a system, I have a long way to go for getting there.

But my sources are Oracle docs, Ixora, Internet, few training materials and of course Oracle server itself with awesome tracing and debugging abilities.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, September 29, 2003 9:09 PM

> "No expert"? Hardly! Tanel, just how the heck do you KNOW all this
> stuff?
>
>
> --- Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> > Hi!
> >
> > As I understand, when shared pool heap is allocated, half of it's
> > memory is
> > actually "hidden" at first. Oracle just allocates one big permanent
> > type
> > chunk for that.
> > The rest of memory is put on shared pool freelist. Initially this is
> > just
> > one big free chunk as well, but starts shrinking as space requests
> > are done
> > from it. One space request might result in multiple allocated chunks,
> > if
> > there's not enough free space in one memory extent for example.
> >
> > When a new chunk is allocated, the allocator will specify size and
> > type of
> > chunk it wants:
> > - permanent type is permanent, unpinnable and unfreeable chunk.
> > Permanent
> > chunks exist until the whole heap is deallocated.
> > - freeable type chunks can explicitly be freed by allocator (there's
> > also
> > special type of freeable chunks, called freeable with a mark, which
> > can be
> > freed implicitly, depending on memory usage in heap)
> > - recreatable type chunks are pinned ("in use") right after
> > allocation and
> > they can't be freed until they are explicitly unpinned.
> >
> > So, when allocating a recreatable type chunk, first freelists are
> > searched
> > for suitably sized free chunks. A heap freelist actually consists of
> > 255
> > different lists, one for each size range of free chunks (smallest
> > size range
> > starts from 16 bytes, largest is about 64k+). This allows the
> > freelist to be
> > scanned faster. When no exactly matching free chunk is found, the
> > next
> > largest will be taken and is split. The leftover free chunk is placed
> > to
> > appropriate range in freelist. Memory allocations & deallocations in
> > shared
> > pool are protected by shared pool latch (by shared pool child latch
> > starting
> > from 9i - you can separate shared pool to several heaps for better
> > concurrency in 9i). AFAIK, Oracle is also able to coalesce adjacent
> > free
> > chunks when they're freed.
> >
> > When a recreatable chunk is allocated, it is marked as "pinned" -
> > meaning
> > currently in use. Thus noone can free it until it is explicitly
> > unpinned by
> > it's allocator (for example, several chunks might be pinned in shared
> > pool
> > during SQL parse and execution, but get unpinned right after the
> > statement
> > has finished). Here comes the LRU list into play. When a recreatable
> > chunk
> > is unpinned first time, it is put into MRU end of *transient* LRU
> > list,
> > since Oracle doesn't know whether it's needed ever again. When it is
> > pinned
> > next time, then of course it's taken off from LRU list at first, but
> > the
> > chunk itself is marked recurrent and is put in *recurrent* LRU list
> > when
> > unpinned again.
> > (Note that I'm not sure how this LRU list internal structure looks
> > like,
> > whether there are really two LRU lists for each heap or is there a
> > single
> > one with two ends).
> >
> > Now, when a new space request is done, first freelists are scanned,
> > but if
> > there is no sufficient space there, transient LRU list is scanned and
> > if big
> > enough unpinned recreatable chunk is found, it is freed and returned
> > to free
> > list.
> > Ok, but what happens if no suitable chunk is found from neither
> > freelists
> > nor LRU list? Oracle will then release "hidden" free space, which is
> > allocated as permanent chunk during startup and is not in any
> > freelists. The
> > reason behind that might be that it is good to have less available
> > memory
> > during database startup, dictionary cache population and various
> > applications initialization operations - that way more transient
> > recreatable
> > chunks can be reused and LRU lists don't get that long and there's
> > less
> > fragmentation in shared pool before "real work" starts. Long LRU and
> > freelists are one reason for shared pool latch contention, that's why
> > one
> > should consider reducing of shared pool in case of this latch problem
> > instead of usual "more memory is better" approach (as mentioned
> > above, in 9i
> > it's possible to split shared pool into several heaps to improve
> > concurrency).
> >
> > And if even hidden memory is used up, then we get ORA-4031.
> >
> > Ok, this was a tiny part of heap management in Oracle, there is
> > actually
> > much more, such reserved list for shared pool reserved area and what
> > happens
> > free chunk split leftovers which are smaller than 16 bytes etc. Since
> > I'm
> > not expert on SGA, please correct if I'm wrong.
> >
> > Tanel.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Friday, September 26, 2003 3:17 PM
> >
> >
> > > ...long long way to go ..... b4 i reach x$ tables.
> > >
> > > Tanel, can u brief me about transient chunks & recurrent chunks
> > > that u were discussing with Steve ?
> > >
> > > Jp.
> > >
> > >
> > > 26-09-2003 19:54:48, "Tanel Poder" <tanel.poder.003_at_mail.ee> wrote:
> > >
> > > >I'd suggest, when possible, not to use any x$ views, but stich
> > with plain
> > > >old documented ways. That way you'll probably avoid a lot of
> > confusion,
> > > >especially when database versions might change..
> > > >
> > > >Tanel.
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Prem Khanna J
> > > INET: jprem_at_kssnet.co.jp
> > >
> > > 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).
>
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Paul Baumgartel
> INET: treegarden_at_yahoo.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).