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" <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).