How much
memory does each of the individual components of the SGA need? Oracle now has
methods to determine most of the major parameters all on its own.
What is the SGA
Simply stated, the system global area (SGA) is just
shared memory structures that are created at instance startup, hold information
about the instance and control its behavior. The following table gives a brief
synopsis of the particular components of the SGA, the variables that control
the size of memory allocated, some of the areas of the Oracle server the
particular component has an influence on, and then a very brief description.
What can be seen from this simple list is that there are plenty of options available
for us to tweak the SGA and without a complete understanding of what our applications
are doing in the background, our ability to guess the appropriate amount of
memory to give each of these individual components is not always optimal. What
we do not want to have happen in this process of allocation of memory is to
waste it.
Components of the SGA
|
SGA
COMPONENT
|
SIZE
CONTROLED BY
|
AREAS OF INFLUENCE
|
SIMPLE DESCRIPTONS
|
|
Shared Pool
|
SHARED_POOL_SIZE
|
Library Cache
Shared SQL areas
Private SQL areas
PL/SQL procedures and packages
Various
control structures
|
Oracle
needs to allocate & deallocate memory as SQL or procedural code is
executed based on the individual needs of users' sessions and in accordance
to the LRU algorithm.
|
|
Dictionary Cache
Row cache
Library
cache
|
Highly
accessed memory structures that provide information on object structures to
SQL statements being parsed.
|
|
Java Pool
|
JAVA_POOL_SIZE
|
Run state
Methods
Classes
Session code
Data
in JVM
|
Memory
available for the Java memory manager to use for all things Java.
|
|
Streams Pool
|
STREAMS_POOL_SIZE
|
Stream
activity
|
New
to Oracle 10g, memory available for stream processing.
|
|
Redo Log Buffer
|
LOG_BUFFER
|
Redo
entries
|
Holds
changes made to data and allows for the reconstruction of data in the case of
failure.
|
|
Database Buffer Cache
|
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
|
Write
list
LRU
list
|
Holds
copies of data requested by SQL and reduces requests to disk by having data
in memory. You may have many different buffer caches that help segregate on usage
patterns.
|
|
Large Pool
|
LARGE_POOL_SIZE
|
Shared server
Oracle XA
I/O server processes
Backup & restore
|
For
large memory allocations.
|
You can look at the size of your
SGA by looking at the initialization parameters that control its size. Here is
a simple query and its output.
select name, value
from v$parameter
where name in ('shared_pool_size', 'java_pool_size', 'streams_pool_size',
'log_buffer', 'db_cache_size', 'db_2k_cache_size', 'db_4k_cache_size',
'db_8k_cache_size', 'db_16k_cache_size', 'db_32k_cache_size',
'db_keep_cache_size', 'db_recycle_cache_size', 'large_pool_size');
NAME VALUE
------------------------- ---------
shared_pool_size 83886080
large_pool_size 8388608
java_pool_size 50331648
streams_pool_size 54525952
db_cache_size 25165824
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
log_buffer 262144
13 rows selected.
» 
Comment and Contribute
