How to Check Oracle Instance Size

Determining the size of Oracle Instance is important for performance reasons. You must allocate adequate RAM for instance to run smoothly. By just allocating more RAM can sometimes result in resource wastage.

Find Memory Used by Oracle Instance

The below query will give you breakdown of the memory used by your Oracle Instance

select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
from
(
select 'sga' nm, sum(value) val
from v$sga
union all
select 'pga', sum(a.value)
from v$sesstat a, v$statname b
where b.name = 'session pga memory'
and a.statistic# = b.statistic#
)
group by rollup(nm);

A very simple query to display SGA components size in Oracle database

SELECT * FROM v$sgainfo;

Oracle Automatic Memory Management

From 11g onward, you do not need to manage SGA and PGA separately. You can allocate MEMORY_TARGET parameter and oracle will handle both SGA + PGA. Oracle MEMORY_TARGET parameter defines the total instance size. Example, If MEMORY_TARGET is set to 5 GB, oracle will manage PGA + SGA within 5 GB

Show parameter memory_target;

alter system set memory_target = 5G;

The MEMORY_MAX_TARGET parameter defines the maximum RAM that Oracle can use in case of heavy workloads. MEMORY_MAX_TARGET is a static parameter

show parameter memory_max_target;

alter system set memory_max_target = 7G scope=spfile;

Things to remember:

• SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA

• SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.

• SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).

• PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

Separate SGA and PGA

Some databases require separate SGA and PGA configuration for application to work smoothly. The SGA_TARGET defines the total SGA size

show parameter sga_target;

alter system set sga_target = 9G;

The SGA_MAX_SIZE defines the maximum RAM SGA can take during heavy workloads. SGA_MAX_SIZE is a static parameter and cannot be changed immediately

show parameter sga_max_size;

alter system set sga_max_size = 12G scope=spfile;

The PGA_AGGREGATE_TARGET defines the PGA size and PGA_AGGREGATE_LIMIT is set to (2 * PGA_AGGREGATE_TARGET).

show parameter pga_aggregate_target;
show parameter pga_aggregate_limit;

Hence, Instance Size = SGA_TARGET + PGA_AGGREGATE_TARGET