Automate the Sizing of your SGA in Oracle 10g
September 2, 2004
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
Components of the SGA
AREAS OF INFLUENCE
Shared SQL areas
Private SQL areas
PL/SQL procedures and packages
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.
accessed memory structures that provide information on object structures to
SQL statements being parsed.
available for the Java memory manager to use for all things Java.
to Oracle 10g, memory available for stream processing.
Redo Log Buffer
changes made to data and allows for the reconstruction of data in the case of
Database Buffer Cache
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
I/O server processes
Backup & restore
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
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');
13 rows selected.