Automate the Sizing of your SGA in Oracle 10g

September 2, 2004



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.
    







    The Network for Technology Professionals

    Search:

    About Internet.com

    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers