Introduction

In the previous article, Instance & Database, Oracle for the SQL Server Guy, we discussed the instance architecture. This module presents an overview of memory architecture and configuration settings.

Memory Architecture Overview

Controlling disk I/O is a key design area of every RDBMS because disk reads/writes are critical resource intensive tasks. Hence an understanding of database memory management certainly helps in optimizing database driven applications.

In Oracle, a set of parameters stored in initialization file(parameter files(pfile) or server parameter file(spfile) which defines the attributes such as memory, network, storage, sessions, character set etc of the database/instance.

SQL Server doesn’t have precise equivalents of every initialization parameter. The configuration options in SQL Server can be specified using the Management Studio, Configuration Tools and using the sp_configure system stored procedure.

The memory structures associated with Oracle are as shown in the diagram below:

In SQL Server, memory structures are categorized into Memory Pool and MemToLeave with sub components as depicted in the diagram below. MemToLeave doesn’t have much relevance in x64 SQL Server versions as suggested by Bob Ward in http://blogs.msdn.com/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx

Let us examine Oracle memory structures and their SQL Server counterparts.

System Global Area (SGA)

SGA is the most important memory component in Oracle which is a group of shared structures. SGA equivalent in SQL Server is the memory pool with subcaches.

Following are the components of the SGA and configurable using parameter file.

1. Database Buffer Cache - This holds copies of data blocks read from data files. The DB_CACHE_SIZE parameter determines the size. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE. Apart from default buffer pool(DB_CACHE_SIZE), two additional buffer pools - KEEP and RECYCLE and the corresponding parameters are DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. 

The Database Buffer Cache  is equivalent to Buffer Cache in SQL Server.

2. Redo Log Buffer - This stores redo entries, log of changes made to the database. LOG_BUFFER  parameter determines the size.

Redo Log Buffer is equivalent to Log Cache in SQL Server.

3. Shared Pool - The Shared Pool contains the library cache, dictionary cache, buffers for parallel execution messages and control structures. SHARED_POOL_SIZE  parameter determines the size.

Shared Pool is equivalent to Procedure Cache in SQL Server.

4. Java Pool - The Java Pool is used for all session-specific java code and data within JVM. The JAVA_POOL_SIZE parameter determines the size.

Java Pool is equivalent to CLR Hosting layer in MemToLeave  in SQL Server

5. Large Pool - The Large Pool is used in shared server, backup and  restore operations. The LARGE_POOL_SIZE parameter determines the size

Large Pool is equivalent to Buffer Pool in SQL Server

6. Data Dictionary Cache - The Data Dictionary Cache is used to hold dictionary data in the form of data rows

Data Dictionary Cache is equivalent to System Structures in SQL Server.

Oracle allocates memory for the SGA whenever the instance is started.

SGA components manage size in units of chunks and the limit is specified by the SGA_MAX_SIZE parameter. If the value for SGA_MAX_SIZE is less than the memory allocated for all components, then the database ignores the setting for SGA_MAX_SIZE.

In SQL Server, size of the memory pool is adjusted using min server memory and max server memory settings.

Automatic Shared Memory Management

In Oracle, apart from manual configuration, automatic memory management can be enabled by using SGA_TARGET parameter. In this setting, Oracle automatically manages the distribution of this memory among the various components of the SGA based on changing workload of the database. This is similar to the dynamic management of Memory Pool in SQL Server.

Oracle can automatically manage the following five components of the SGA

1. Database Buffer Cache(DB_CACHE_SIZE)
2. Shared Pool(SHARED_POOL_SIZE)
3. Large Pool(LARGE_POOL_SIZE)
4. Java Pool(JAVA_POOL_SIZE)
5. Streams Pool(STREAMS_POOL_SIZE)

SGA components that need to be managed even in automatic shared memory management are,

1. Keep Buffer Cache (DB_KEEP_CACHE_SIZE)
2. Recycle Buffer Cache(DB_RECYCLE_CACHE_SIZE)
3. Any non-standard block size buffer caches(DB_nK_CACHE_SIZE)
4. Redo Log Buffer(LOG_BUFFER)

Program Global Area (PGA)

Oracle creates a program global area (PGA) for each user session. Unlike the SGA, the PGA is for the exclusive use of each user. PGA memory is classified into the following types:

Private SQL Area: This area of memory holds SQL variable bind information and runtime memory structures. Parameters associated include SORT_AREA_SIZE, HASH_AREA_SIZE and BITMAP_AREA_SIZE.

Runtime area: The runtime area is created for a user session when the session issues SELECT, INSERT, UPDATE or DELETE statement which includes complex joins or heavy sorting.

PGA memory allocation can be automated by setting WORKAREA_SIZE_POLICY initialization parameter to auto, the default setting. In manual mode it is required to specify vale for all the PGA related parameters.  Set the size of the total PGA memory using PGA_AGGREGATE_TARGET parameter.

PGA components can be mapped to  Connection Context and Buffer cache(Stolen buffer concept) in SQL Server. 

User Global Area(UGA)

In Oracle, based on the setup, there are two types of configurations, dedicated server, one-to-one mapping between server process and  user process and shared server, one-to-many relationship between a shared server process and multiple user processes.

UGA is part of PGA in dedicated server environment. UGA moved to large pool

or shared_pool in shared server environment. UGA has user session data, cursor state, sort areas, private SQL areas.

The equivalent components in SQL Server are Connection Context and Buffer Pool.

In Oracle 11g, there is a configurable parameter, MEMORY_TARGET which can be specified as the cumulative value of both SGA and PGA  at the instance level.  In this case, system can automatically distribute the required memory among SGA and PGA as and when required based on system workload.

MEMORY_MAX_TARGET is the maximum possible value for MEMORY_TARGET.

The mappings can be summarized as,

Conclusion

In Oracle, compared to SQL Server, there are several parameters that are used to control memory allocation to the instance and its sub-components. Oracle allows granularity in user control down to specific memory areas but Microsoft adopted dynamic memory management completely by default.

References

1.     SQL Server 2005 Books online

2.     http://tahiti.oracle.com