Oracle Memory Management

It has been a few months since i added a new post in my blog. I moved to Austin, TX last December 2009 and since that time, i have been busy with my new job, settling down with new place, buying new home etc. I am going to discuss about Oracle Memory components and their function of each component. I am writing this article based on my experience, reading oracle websites, books and other blogs.

First let me explain about each memory component in oracle. Oracle Memory is divided into two portion. One is System Global Area(SGA) and another one is Program Global Area(PGA). Let us now discuss about SGA now. Then we will talk about PGA in the later portion of this article.

System Global Area consists of following Memory component.

1. Fixed size
2. Variable size
3. DB Buffer cache
4. Redo log buffer

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning option

SQL> show sga

Total System Global Area 1654013952 bytes
Fixed Size 2103048 bytes
Variable Size 1342179576 bytes
Database Buffers 301989888 bytes
Redo Buffers 7741440 bytes
SQL>

SGA = Fixed size + Variable size + DB buffers + Redo buffers

What is Fixed size?

The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. This memory varies in size from platform to platform and release to release. This is something over which we have no control and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

What is Variable size?

Variable size is sum of shared pool, java pool, large pool and streams pool. The streams pool is added in oracle10g.

Shared pool = library cache + dictionary cache

Variable size = Shared pool + Java pool + large pool + streams pool

Let us briefly talk about each component in variable size.

Shared pool : It consists of Library cache and Dictonary cache.

The library cache stores shared SQL, caching the parse tree and the execution plan for every unique SQL statement. If multiple applications issue the same SQL statement, each application can access the shared SQL area. This reduces the amount of memory needed and reduces the processing-time used for parsing and execution planning.

The dictionary cache stores dictionary information in order to parse the SQL statement. It also contains user information, integrity constraints defined for tables etc.

Java pool : It is used for caching parsed java programs.

Large pool : is used in shared/MTS server systems for session memory, by parallel execution for message buffers, and by RMAN backup processes for disk I/O buffers.

Stream pool : is used for streams.

What is DB buffer cache? It stores the recently executed database blocks. When other user fires the same query, oracle reads from buffer cache instead of accessing physical files.

What is Redo log buffer? It stores all the changes made in the database. It will be stored in archive log files for recovery purpose.

Please click to know more info about SGA

Program Global Area(PGA) A PGA is allocated by Oracle when a user connects to an Oracle database and a session is created. PGA contains session information and Private SQL area.

Content of PGA :

1. Session information. For instance, Logon info, session related info.

2. Private SQL area. Please remember, this is part of PGA only if the DB is dedicated server. This memory will be part of SGA if the DB is shared server. Private SQL area contains informatoin about the cursor, query execution status info, sql work area(sort area). Please click here to know more about PGA memory.

Oracle keep introducing new features in the memory management side to make DBA's life easy.

Let us talk about new oracle memory management features in each new version since from Oracle9i.

1. Oracle9i Feature

2. Oracle10g Feature

3. Oracle11g Feature