Unanswered: Sizing SGA for Oracle based application
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?
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!
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.
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 */
IF ( l_numusers = 0) THEN
dbms_output.put_line('Maximum concurrent users on this database = '
dbms_output.put_line('Calculating SGA for = '
||TO_CHAR(l_numusers)||' concurrent users');
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
sum(sharable_mem) INTO l_sum_sql_shmem
sum(sharable_mem) INTO l_sum_obj_shmem