top of page
  • Writer's pictureArun 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:

5,705 views

Recent Posts

See All

With ASM configured for RAC or NON-RAC systems, it is a good idea to move the spfile to ASM. The PFILE under $ORACLE_HOME/dbs location actually points to the SPFILE on ASM disk. Create PFILE from SPFI

bottom of page