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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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



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.
    


    Oracle Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    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


















    Thanks for your registration, follow us on our social networks to keep up-to-date