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" <[EMAIL PROTECTED]>
Sent: Monday, September 29, 2003 9:09 PM
> "No expert"? Hardly! Tanel, just how the heck do you KNOW all this
> stuff?
>
>
> --- Tanel Poder <[EMAIL PROTECTED]> 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 leftove
Re: (long and boring) "SQL AREA" and "LIBARARY CACHE" size?
"No expert"? Hardly! Tanel, just how the heck do you KNOW all this
stuff?
--- Tanel Poder <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
> 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" <[EMAIL PROTECTED]> 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
Re: (long and boring) "SQL AREA" and "LIBARARY CACHE" size?
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" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
