Dec 31, 2013

PGA & SGA

PGA stands for Process Global Area which is also known as Program Global Area. It is called global area because is keeps information which is required by all modules of Oracle Code. PGA keeps information specific to the sever process upon which Oracle code acts.

PGA also keeps information about oracle shared resources so that it can free those resources .
Each session contains specific information like bind variables and runtime structures in a private SQL area.

Whenever a session executes a statement, a private SQL area is assigned to that session.
Even if multiple users are issuing the same statement using the same shared SQL area, each session will have its own dedicated private SQL area.

A private SQL area contains data such as bind information and runtime memory structures.
Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area.

Thus, many private SQL areas can be associated with the same shared SQL area. A private SQL area itself is divided into run-time area and persistent area.

Persistent area contains information like bind variable and will be freed once the cursor is closed.
Run-time area is allocated with the first step of the execute request and will be freed when execution is completed.

SGA
Each instance will have its own SGA .The SGA contains data buffer areas, redo log buffers and the shared pool .
Each area is important to the database overall performance.

The Oracle kernel process uses an LRU (Least Recently Used) algorithm to write data back to the disks.
Data is never altered on the disks directly, but is altered in memory first.

The shared SQL pool is used to store the Dictionary Cache as well as information about SQL statements that are being run against the database.

No comments: