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

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.

    James Koopmann
    James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

    Latest Articles