Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: Sizing SGA for Oracle based application

    Hi all,

    We have developed an internet based e-bussiness application and we are using oracle 8i. Although we have designed the complete physical database/schema and it is operational.

    Now we are planning to go live on production and plan to have V880 sun sparc servers with 4 GB RAM. I would like you all to share your experience in capacity planning specially sizing the SGA.

    1. Is there any methodological way to come up with proper initial size for the SGA and its components – buffer cache size, shared pool size, large pool size and log buffer size? Please note the word ‘initial’, I am aware how to monitor SGA and suggested modification in the parameters. But what abt ‘initial’?
    2. The Number and size of the Redo logs?
    3. The Number and Size of the Rollback Segments?

    Just to give you an idea, the size of the database is approx. 80 GB and the total RAM of the server would be 4 GB. There are around 300 tables and a lot of packages/procedures.

    Also, are there any documents available on net, can anyone provide details?
    Also, are any documents available with anyone who would like to share with me?


  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    It depends!
    SGA size (shared pool) should be larger than the total size of all packages & procedures involved with the application and pinned into the SGA when the instance starts.
    ROLLBACK should be sized so that you don't run out of them.
    This is a function of the number & size of concurrent transactions.
    REDO should be sized so that they switch around every 30 minutes under "normal" transaction load.
    Keep in mind that when running Oracle on Solaris "free memory" ALWAYS is reported as a "small" number but greater than zero;
    this happens regardless of the actual amount of RAM.
    HTH & YMMV!

  3. #3
    Join Date
    Aug 2003

    Try this one ...

    Sizing Oracle System Global Area


    This script will calculate the size of the SGA of an existing database based upon the current usage. This script is particularly useful for getting a SGA size of an existing development database and scaling up the figures for a production database that does not yet exist. Run this script on the development database and alter the number of users constant (l_numusers) to be what is expected on the production database for the number of concurrent users. It assumes that there is 30% free memory on top of the calculated. This can be altered by changing the l_uplift PL/SQL constant.


    The script default to using the maximum number of concurrent users
    which have logged on, but you can amend this script to calculate for any number
    by changing the PL/SQL constant l_numusers.

    SQL Source

    set serverout on
    l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */

    l_numusers NUMBER DEFAULT 50; /* Change this to a predicted number if not an existing database */
    l_avg_uga NUMBER;
    l_max_uga NUMBER;
    l_sum_sql_shmem NUMBER;
    l_sum_obj_shmem NUMBER;
    l_total_avg NUMBER;
    l_total_max NUMBER;

    IF ( l_numusers = 0) THEN
    SELECT sessions_highwater
    INTO l_numusers
    FROM v$license;
    dbms_output.put_line('Maximum concurrent users on this database = '
    dbms_output.put_line('Calculating SGA for = '
    ||TO_CHAR(l_numusers)||' concurrent users');
    END IF;

    INTO l_avg_uga, l_max_uga
    FROM v$sesstat s, v$statname n
    WHERE s.statistic# = n.statistic#
    AND = 'session uga memory max';

    sum(sharable_mem) INTO l_sum_sql_shmem
    FROM v$sqlarea;

    sum(sharable_mem) INTO l_sum_obj_shmem
    FROM v$db_object_cache;

    l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
    l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;

    dbms_output.put_line('Recommended Shared_pool size between :'
    || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) )
    ||' and '
    || TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) )
    ||' bytes');

    dbms_output.put_line('Recommended Shared_pool size between :'
    (l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
    ||' and '
    (l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) )
    ||' M bytes');


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts