Databases Reference
In-Depth Information
Java pool
Provides memory allocation for Java objects and Java execution, including data in
the Java Virtual Machine in the database.
Streams pool
Provides memory allocation used to buffer Oracle Streams queued messages in the
SGA instead of in database tables and provides memory for capture and apply. Note
that Oracle GoldenGate is now the recommended solution, instead of Streams, for
queuing messages to be delivered among databases.
Dynamic initialization parameters available for these pools include LARGE_POOL
_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. These are automatically set
if MEMORY_TARGET or SGA_TARGET is specified.
Automatic PGA management
Oracle automatically manages the memory allocated to an instance Program Global
Area (PGA). The PGA consists of session memory and a private SQL area. There is a
PGA allocated for each service, which corresponds to a pluggable database in Oracle
Database 12
c
. The memory amount can be controlled by setting the PGA_AGGRE‐
GATE_TARGET initialization parameter. Automatic PGA management, available since
Oracle Database 10
g
, greatly simplified management of SQL work areas and eliminated
the need to set several different initialization parameters that previously existed. As of
Oracle Database 11
g
, PGA memory allocation is automatically tuned along with the
SGA memory allocations by setting MEMORY_TARGET. With Oracle Database 12
c
,
this control has been refined by adding a parameter for the PGA_AGGREGATE_LIMIT.
This parameter sets a hard limit on the total amount of memory that the PGA can use;
when this limit is reached, the sessions using the greatest amount of the PGA are paused
until the memory usage drops.
Background Processes for an Instance
The most common background processes are shown in
Figure 2-8
and vary from Oracle
release to release. Among the background processes in Oracle Database 12
c
are the
following:
Database Writer (DBWn)
Writes database blocks from the database buffer cache in the SGA to the datafiles
on disk. An Oracle instance can have up to 20 DBW processes to handle the I/O
load to multiple datafiles—hence the notation DBW
n
. Most instances run one
DBW. DBW writes blocks out of the cache for two main reasons:
• If Oracle needs to perform a checkpoint (i.e., to update the blocks of the datafiles
so that they “catch up” to the redo logs). Oracle writes the redo for a transaction
when it's committed, and later writes the actual blocks. Periodically, Oracle
