How to Check Oracle Instance Size
Determine the size of your Oracle database instance.
The first step is to understand how instance size is configured. There are two main configurations:
One Instance Size
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;If you see memory_target value blank, then separate SGA and PGA are configured
The MEMORY_MAX_TARGET parameter defines the maximum RAM that Oracle can use in case of heavy workloads. Example, if MEMORY_TARGET is set to 5 GB and MEMORY_MAX_TARGET is set to 8 GB, then during heavy workloads, Oracle instance can max take upto 8 GB RAM
show parameter memory_max_target;Separate SGA and PGA
Some databases require separate SGA and PGA configuration for application to work smoothly. The SGA_TARGET defines the SGA size and SGA_MAX_SIZE defines the maximum RAM SGA can take during heavy workloads. Example, if SGA_TARGET is set to 3 GB and SGA_MAX_SIZE is set to 5 GB, then during heavy workloads, SGA can max take upto 5 GB of RAM
show parameter sga_target;
show parameter sga_max_size;The PGA_AGGREGATE_TARGET defines the PGA size and PGA_AGGREGATE_LIMIT is set to (2 * PGA_AGGREGATE_TARGET). Example, if PGA_AGGREGATE_TARGET is set to 4GB, then set PGA_AGGREGATE_LIMIT to 8GB (2 * PGA_AGGREGATE_TARGET)
show parameter pga_aggregate_target;
show parameter pga_aggregate_limit;Hence, Instance Size=SGA_TARGET+PGA_AGGREGATE_TARGET
Further Read:
