• Arun Kumar

How to Check Oracle Instance Size

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:

2,108 views

Recent Posts

See All

Oracle database refresh is the most common activity in any environment. There are multiple ways to perform the database refresh. Here you will find most common methods that are used for refresh activi