Monitoring SGA (Free Memory) Using v$sgastat

In this article, we will discuss how to get monitor free memory in System Global Area (SGA) in Oracle 10g. The System Global Area (SGA) Memory Structures in data. The SGA is read/write.  A system global area is a group of shared memory areas that dedicated to an Oracle database instance. For example Large Pool, Shared Pool, Java Pool etc.

SQL> SHOW SGA;
Total System Global Area  608174080 bytes
Fixed Size                  2006160 bytes
Variable Size             176161648 bytes
Database Buffers          423624704 bytes
Redo Buffers                6381568 bytes

Let’s use v$sgastat parameter in this article. The v$sgastat parameter displays detailed information on the system global area (SGA).

SQL> DESC v$sgastat;
Name                                      Null?    Type
----------------------------------------- -------- ----------------
POOL                                               VARCHAR2(12)
NAME                                               VARCHAR2(26)
BYTES                                              NUMBER

Let’s have a short explanation of all column of the v$sgastat parameter:

POOL: Designates the pool in which the memory in NAME resides:

• Shared Pool: Memory is allocated from the shared pool.

• Large Pool: Memory is allocated from the large pool.

• Java Pool: Memory is allocated from the Java pool.

• Streams Pool: Memory is allocated from the Streams pool.

NAME: The name column means system global area component name.

BYTES: The memory size in bytes.

Now we will use a simple SQL to see the free memory in SGA.

SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
  2  From V$sgastat
  3  Where Name Like '%free memory%';
POOL         FREE_MEMORY_IN_MB
------------ -----------------
shared pool                 22
large pool                   3
java pool                    4
streams pool                 8