Oracle 内存结构-读书小记

Oracle's three major memory structures:

• System Global Area (SGA): This is a large, shared memory segment that virtually all
Oracle processes will access at one point or another.
• Process Global Area (PGA): This is memory that is private to a single process or
thread; it is not accessible from other processes/threads.
• User Global Area (UGA): This is memory associated with your session. It is located
either in the SGA or the PGA, depending whether you are connected to the
database using a shared server (it will be in the SGA), or a dedicated server (it will
be in the PGA).

 

The SGA is broken up into various pools. Here are the major ones you’ll see:
• Java pool: The Java pool is a fixed amount of memory allocated for the JVM
running in the database. In Oracle10g, the Java pool may be resized online while
the database is up and running.
• Large pool: The large pool is used by shared server connections for session
memory, by parallel execution features for message buffers, and by RMAN
backup for disk I/O buffers. This pool is resizable online.
• Shared pool: The shared pool contains shared cursors, stored procedures, state
objects, dictionary caches, and many dozens of other bits of data. This pool is
resizable online in both Oracle 10g and 9i.
• Streams pool: This is a pool of memory used exclusively by Oracle Streams, a datasharing
tool within the database. This pool is new in Oracle 10g and is resizable
online. If the Streams pool is not configured and you use the Streams
functionality, Oracle will use up to 10 percent of the shared pool for streams
memory.
• The “Null” pool: This one doesn’t really have a name. It is the memory
dedicated to block buffers (cached database blocks), the redo log buffer, and a
“fixed SGA” area.
A typical SGA might look as shown in Figure:

The parameters that have the greatest effect on the overall size of the SGA are as follows:
• JAVA_POOL_SIZE: Controls the size of the Java pool.
• SHARED_POOL_SIZE: Controls the size of the shared pool (to some degree).
• LARGE_POOL_SIZE: Controls the size of the large pool.
• DB_*_CACHE_SIZE: Eight of these cache_size parameters control the sizes of the
various buffer caches available.
• LOG_BUFFER: Controls the size of the redo buffer (to some degree).
• SGA_TARGET: Used with automatic SGA memory management in Oracle 10g and
above.
• SGA_MAX_SIZE: Used to control the maximum size to which the SGA can be resized
while the database is up and running.
• MEMORY_TARGET: Used with automatic memory management (both PGA and SGA
automatic memory management).
• MEMORY_MAX_SIZE: Used to control the maximum amount of memory Oracle should
strive to use over both the PGA and SGA sizes under automatic memory
management. This is really just a target; the PGA may exceed the optimum size if
the number of users increases beyond some level or a session(s) allocates large
untunable bits of memory as demonstrated above.

 

注：摘自Thomas Kyte 经典著作