Automate the Sizing of your SGA in Oracle 10g

Thursday Sep 2nd 2004 by James Koopmann
Share:

There are plenty of options available for tweaking the SGA. However, 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. Oracle now has methods to determine most of the major parameters all on its own.



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.
    

    Letting Oracle Take Control

    Using Oracle's Automatic Shared Memory Tuning, you can instruct Oracle to manage a subset of the components that make up the SGA by merely telling the instance the target size of the SGA through the new SGA_TARGET parameter. Oracle will then pool from this value and dynamically distribute memory across selected components of the SGA. You now need not set values for SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, or DB_CACHE_SIZE as Oracle will automatically size them for you. Once you set the SGA_TARGET parameter to a desirable size for your SGA these parameters will take on a value of zero and new parameters will be created designated by __SHARED_POOL_SIZE, __JAVA_POOL_SIZE, LARGE_POOL_SIZE, and __DB_CACHE_SIZE. As workloads go through the system and memory is needed in these areas, Oracle will allocate more memory based on internal statistics trends. Oracle will not manage the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DBnK_CACHE_SIZE, or the STREAMS_POOL_SIZE and you must still determine the value for these parameters. In order for this all to take place, you must be using an SPFILE as this is the only way Oracle can dynamically make all these changes happen. Also, note that SGA_TARGET is the sum of all parameters that make up the SGA, not just the parameters it controls, so you must take those components it does not control into consideration when you give a value for SGA_TARGET.

    Stepping through Letting Oracle Take Control

    There is really nothing to switching into automatic shared memory tuning. You only need to set the SGA_TARGET parameter.

    1. Take a look and see if you are already in automated sizing of SGA

    SQL> show parameter sga_target
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------
    sga_target                           big integer 0
    

    2. Alter system to begin automated sizing of SGA

    SQL> alter system set sga_target=216m;
    System altered.
    

    3. Done

    What happens when you switch to Automatic Shared Memory Tuning is a bit interesting. After you alter SGA_TARGET parameter, your SPFILE will undergo a change and now have the following parameters defined. Note that k101 is my instance name and will take on whatever the instance name is.

    k101.__db_cache_size=25165824
    k101.__java_pool_size=50331648
    k101.__large_pool_size=8388608
    k101.__shared_pool_size=83886080
    

    In addition, when you issue the previously given SQL to show the parameter settings for the individual components of the SGA you will notice that they now have a value of zero.

    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          0
    large_pool_size           0
    java_pool_size            0
    streams_pool_size         0
    db_cache_size             0
    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.
    

    If you truly want to see the parameters after setting the SGA_TARGET you will need to modify the query to include the newly created underscore variables. This can be of some concern if you are relying on the "normal" parameters for any database monitoring scripts.

    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        67108864
    __large_pool_size         4194304
    __java_pool_size          8388608
    streams_pool_size         0
    __db_cache_size           142606336
    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.
    

    Switching over to the Automatic Shared Memory Tuning is as easy as setting an initialization parameter. How this will behave under load is yet to be determined but since these numbers are driven by the various advisories and I am mostly happy with them as individual components, I see no reason not to venture down the path of having Oracle automatically size my SGA. Of course, as always, in a test environment first. I would suggest you take a snapshot of your initialization parameters before letting Oracle take control and then compare the end settings that Oracle has implemented. It is always easy to switch back, just reset the SGA_TARGET parameter and set the individual components back to their original values.

    » See All Articles by Columnist James Koopmann

    Share:
    Home
    Mobile Site | Full Site
    Copyright 2017 © QuinStreet Inc. All Rights Reserved