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
| 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
| Highly accessed memory structures that provide information on object structures to SQL statements being parsed. | ||
| Java Pool | JAVA_POOL_SIZE |
| Memory available for the Java memory manager to use for all things Java. |
| Streams Pool | STREAMS_POOL_SIZE |
| New to Oracle 10g, memory available for stream processing. |
| Redo Log Buffer | LOG_BUFFER |
| 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 |
| 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 |
| 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.