SGA Tuning

There are two parameters
SGA_TARGET: RAM SPACE IS ASSIGNED TO ORACLE SERVICES, CAN SEE ON TASK MANAGER PROCESS

This parameter is new with Oracle 10g. It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.

sga_target cannot be higher than sga_max_size.


If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.



SGA_MAX_SIZE

This parameter sets the hard limit up to which sga_target can dynamically adjust sizes. Usually, sga_max_size and sga_target will be the same value, but there may be times when you want to have the capability to adjust for peak loads. By setting this parameter higher than sga_target, you allow dynamic adjustment of the sga_target parameter.

SGA Sizing on a dedicated server
OS Reserved RAM – This is RAM required to run the OS kernel and system functions, 20% of total RAM for MS-Windows, and 10% of total RAM for UNIX/Linux

Oracle Database Connections RAM – Each Oracle connection requires OS RAM regions for sorting and hash joins. (This does not apply when using the Oracle multi-threaded server or pga_aggregate_target .) The maximum amount of RAM required for a session is as follows:

2 MB RAM session overhead + sort_area_size + hash_area_size

Oracle SGA Sizing for RAM – This is determined by the Oracle parameter settings. The total is easily found by either the show sga command or the value of the sga_max_size parameter.


Eg. RAM=16 GB

for windows processes = 16*20%=3.5 GB reserved for windows

2MB + 64 KB + 128 KB




ALTER SYSTEM SET SGA_TARGET=6512M;

ALTER SYSTEM SET SGA_MAX_SIZE=8152M SCOPE=SPFILE;