Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 2, 2004

Automate the Sizing of your SGA in Oracle 10g

By James Koopmann

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



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.


  • 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.

    Oracle Archives

    Latest Forum Threads
    Oracle Forum
    Topic By Replies Updated
    Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
    Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
    no matching unique or primary key rcanter 1 April 25th, 12:32 PM
    Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM