Dive Deep into Oracle DB: Overview of Memory Architecture (1)

Oracle uses memory to store information such as

  • Program code
  • Information about a connected session, even if it is not currently active
  • Information needed during program execution (for example, the current state of a query from which rows are being fetched)
  • Information that is shared and communicated among Oracle processes (for example, locking information)
  • Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)
The basic memory structures associated with Oracle include:

  • System Global Area (SGA) and
  • Program Global Areas (PGA).

System Global Area

Group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users.
Contains general information about the state of the database and the instance, which the background processes need to access.This is called the fixed SGA. No user data is stored here.
Includes information communicated between processes, such as locking information.

Program Global Area

Contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it.
Process SQL statements and to hold logon and other session information.
Large part of the PGA is dedicated to SQL work areas and other SQL operations.