• 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:

718 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against