RelationalDBDesignRelationalDBDesign





Memory Architecture   «Prev 

An alternate meaning for SGA

Shared Global Area is the same as System Global Area

The acronym SGA stands for System Global Area. Because it's a shared memory structure, and because the word “shared” also begins with an “S,” people sometimes refer to the SGA as the Shared Global Area. This is such a common occurrence that Oracle even makes mention of it in their manuals. I always use the correct term, but not everyone will. So if you hear someone talk about the Shared Global Area, just mentally translate that to System Global Area.

Automatic Memory Management

Oracle strongly recommends the use of automatic memory management to manage the memory on your system. Automatic memory management enables Oracle Database to automatically manage and tune the instance memory. Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory allocations, consider using the Memory Advisor for managing memory



Considering Multiple Buffer Pools

A single default buffer pool is generally adequate for most systems. However, users with detailed knowledge of an application's buffer pool might benefit from configuring multiple buffer pools. With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day). Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache. When an object is associated with a cache, all blocks from that object are placed in that cache. Oracle Database maintains a DEFAULT buffer pool for objects that have not been assigned to a specific buffer pool. The default buffer pool is of size DB_CACHE_SIZE. Each buffer pool uses the same Least Recently Used (LRU) replacement policy (for example, if the KEEP pool is not large enough to store all of the segments allocated to it, then the oldest blocks age out of the cache).
By allocating objects to appropriate buffer pools, you can:
  1. Reduce or eliminate I/Os
  2. Isolate or limit an object to a separate cache

Databases and Instances

Many Oracle practitioners use the terms instance and database interchangeably. In fact, an instance and a database are different entities, but they are still related. This distinction is important because it provides insight into Oracle's architecture. In Oracle, the term database refers to the physical storage of information, and the term instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server.
  1. The database is physical: it consists of files stored on disks.
  2. The instance is logical: it consists of in-memory structures and processes on the server.
For example, Oracle uses an area of shared memory called the System Global Area (SGA) and a private memory area for each process called the Program Global Area (PGA). An instance can be part of one and only one database, although multiple instances can be part of the same database. Instances are temporal, but databases, with proper maintenance, last forever. Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.