Re: Shrinking PGA of snp processes

Hi,

> Yes, starting from 9i, if you use automatic pga, pga memory is
> released back to OS as needed, without exiting the process.

Actually, it's the UGA and CGA heaps which can be released back to OS starting from 9i (maybe there's some trick for pure PGA memory as well, but I doubt it, because you cannot release some random chunks of a heap, you have to release the whole heap if you want to get some memory back).

Starting from 9i, there is a parameter called _use_realfree_heap, if it is true then Oracle allocates UGA and CGA heaps directly from OS (as top-level heaps). If the parameter is false, Oracle will allocate PGA as top-level heap and then will allocate UGAs and CGAs inside PGA. This means that PGA will grow as more memory is needed but will never shrink as Oracle doesn't do physical heap shrinking, you can free the memory by getting rid of the whole heap.

Also, realfree memory management uses mmap() and munmap() calls on /dev/zero (nice trick) to allocate/deallocate memory, the old fashioned way was to use brk(). To reduce number of overhead/system calls for continuous allocation and deallocation of memory Oracle preallocates more memory in chunks controlled by _realfree_heap_pagesize_hint parameter, that way we don't have to do a separate system call for getting few more bytes for growing our heap. Oracle doesn't release the memory back to OS immediately either - it rather wastes a bit of memory (which is likely gonna be reused anyway) instead of trying to do housekeeping all the time. This "wasted" memory has always been pretty much bogus issue anyway, as this is virtual memory and can be easily paged out while not in use (well, unless you use intimate shared memory for your PGAs under Solaris using _use_ism_for_pga parameter ;) Also, as long as you haven't touched the newly allocated virtual memory page, no physical memory is allocated for it anyway.

The _use_realfree_heap parameter's value defaults to true in 10g and defaults to true on 9.2 too IF pga_aggregate_target is > 0.

There are couple of statistics in v$pgastat which give us indication about freeable PGA:
- PGA memory freed back to OS
- total freeable PGA memory.

The names speak for themselves...

I haven't tested on other platforms, but at least on Linux 2.4 Oracle 10.1.0.3 is using some clever trick - whenever more memory is needed, we actually allocate a bulk range of process virtual memory address space without actually allocating any virtual memory pages for it. It's done by calling mmap() with MAP_NORESERVE flag, this means that no swap space will be reserved for this process, thus we might get unexpected results when we'd start writing into the page, but we'll know the range of this new virtual memory now. When we actually want to use this memory, we'll start mapping virtual memory pages into this freshly allocated address space in smaller quantities (in sizes of _realfree_heap_pagesize_hint value)- using mmap() with MAP_FIXED flag which allows us to manually specify to which address in process address space should the virtual memory page to be mapped.

I guess that the reason behind that is that allocating/extending/initializing address space in OS kernel process structure is heavier operation than mapping memory to it - so it's reasonable to do those address space operations less frequently and use lightweight mapping operations (mmap() with MAP_FIXED) to make this memory actually usable for our process (or maybe the os process structure will just be smaller if you do less bigger allocations instead of more smaller ones...)

The "bulk" address space allocation sizes can be controlled using _pga_large_extent_size and _uga_cga_large_extent_size parameters, for PGA and UGA/CGA respectively. I guess increasing those might be helpful in extreme environments which are frequently allocating/deallocating big PL/SQL arrays or doing frequent hash joins/bitmap merging - where allocating memory would become more significant overhead than the CPU usage itself (I'm only guessing this, as I haven't seen such a system yet).

...

And now when we finally access this memory, it's up to OS VM manager and hardware MMU to find physical pages of memory for our data and estabilish low level address translation between virtual and physical memory page...

Huh, now that I see the length of this post... thanks to the ones who did read through it ;)
Tanel.

--
http://www.freelists.org/webpage/oracle-l