Home > Monitoring SGA Using v$sgastat > Monitoring SGA (Free Memory) Using v$sgastat

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
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: