Automate the Sizing of your SGA in Oracle 10g

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

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.