Oracle Instance Size

How to Check Oracle Instance Size

by

We have two configurations to configure Oracle Instance Size, listed below.

  1. One instance size
  2. Separate Instance size

Oracle Instance Size

Oracle Instance Size

1. One Instance Size: The Oracle MEMORY _TARGET parameter defines the total size of the instance.

Example: If MEMORY _TARGET is set to 10 GB, Oracle will manage PGA + SGA within 10 GB.

SQL> show parameter memory_target

Note: If the output of the above command’s memory_target value is blank, then SGA and PGA are separately configured.

MEMORY_MAX_TARGET defines the maximum RAM that Oracle can use during heavy workloads.

Example: Suppose the MEMORY_TARGET is set to 10 GB and MEMORY _MAX_TARGET is set to 12 GB, the Oracle instance can use a maximum of 12 GB of RAM during heavy workloads.

SQL> show parameter memory_max_target

2. Separate Instance Size: Sometimes we need to set SGA and PGA separately so that the application can work smoothly.

In that case, we have two parameters for SGA, SGA_TARGET, and SGA_MAX_TARGET. The SGA_TARGET defines the SGA size and the SGA_MAX_TARGET defines the minimum size of RAM that could be used by the SGA during heavy workloads.

Example: If we set the SGA_TARGET=4G and SGA_MAX_TARGET=6G so during heavy workloads then SGA uses a maximum 6GB RAM.

SQL> show parameter sga_target
SQL> show parameter sga_max_target

The PGA size is defined by the two parameters PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT.

Example: If the PGA_AGGREGATE_TARGET set 3GB then set PGA_AGGREGATE_LIMIT=2*PGA_AGGREGATE_TARGET, value is 6GB.

show parameter pga_aggregate_target;
show parameter pga_aggregate_limit;

What is Oracle Instance Size

So here is the calculation of oracle instance size.

Instance size = SGA_TARGET + PGA_AGGREGATE_TARGET

How do I find the size of an Oracle Database?

Follow us on

Leave a Comment