Database Reference
In-Depth Information
region, the UGA (user global area), that holds session and cursor state information. Dedicated
server processes allocate UGA memory inside the PGA, whereas shared server processes place
the UGA inside the SGA, since it must be accessible to all shared server processes. If the SGA
contains a large pool (parameter
LARGE_POOL_SIZE
), shared server processes place the UGA
inside the large pool. In Oracle10
g
, the shared pool, large pool, java pool, streams pool, and the
default buffer pool with standard block size
4
can be sized automatically and dynamically with
Automatic Shared Memory Management (parameter
SGA_TARGET
).
In releases prior to Oracle9
i
, several
*_AREA_SIZE
parameters had to be used to adjust the
sizes for various PGA memory regions. Examples of these parameters are
SORT_AREA_SIZE
and
HASH_AREA_SIZE
. On UNIX, where the ORACLE DBMS is implemented as a multiprocess archi-
tecture, PGA memory could not always be returned to the operating system after a memory-
intensive operation. It lingered within the virtual address space of the server process and may
have caused paging. Memory thus allocated was also not available to other server processes.
There was also no instance-wide limit on PGA memory regions. Since each server process was
allowed to allocate memory for an operation up to the limits imposed by
*_AREA_SIZE
parame-
ters, the instance-wide memory consumption could become extensive in environments with
several hundred server processes. Note also that the
*_AREA_SIZE
parameters enforce a per
operation limit, not a per session limit. Since a query may open several cursors simultaneously
and each might execute an expensive
SELECT
that includes an
ORDER
BY
or a hash join, there
is no limit on overall memory consumption with the old approach now called manual PGA
memory management.
To address these shortcomings, automatic PGA memory management was introduced
with Oracle9
i
. On UNIX, it is based on the modern technology of memory mapping, which
enables a process to allocate virtual memory and to map it into its virtual address space. Once
the memory is no longer needed, it can be returned to the operating system by removing the
mapping into the virtual address space. On Solaris, the UNIX system calls used are
mmap
and
munmap
. Calls to memory mapping routines by the ORACLE kernel may be traced using
truss
(Solaris) or
strace
(Linux).
5
Another interesting utility is
pmap
(Solaris, Linux). It displays infor-
mation about the address space of a process, which includes anonymous memory mapped
with
mmap
. Back in the old days of 32-bit computing, this tool provided precisely the information
needed to relocate the SGA base address to allow mapping of a larger shared memory segment
into the limited virtual address space of a 32-bit program (see Metalink note 1028623.6). Using
pmap
while a process sorts, reveals how many regions of anonymous memory it has mapped
and what their cumulative size is.
Here's an example (29606 is the UNIX process ID of the server process found in
V$PROCESS.SPID
). The relevant column is “Anon” (anonymous mapped memory).
$ pmap -x 29606 | grep Kb
Address Kbytes RSS Anon Locked Mode Mapped File
total Kb 934080 888976 63008 806912
4.
Oracle10
g
supports up to seven buffer pools: five buffer pools varying in block size from 2 KB to 32 KB
and two additional buffer pools with standard block size, i.e., the block size set with the parameter
DB_BLOCK_SIZE
. The latter two buffer pools are the keep and recycle pools. Segments may be placed
into the keep or recycle pool with an
A
LTER TABLE
or
ALTER INDE
X
statement as appropriate.
The web page titled
Rosetta Stone for UNIX
at
http://bhami.com/rosetta.html
lis
ts system call tracing
utilities for common UNIX systems.
5.
