Oracle 11g Memory Structure

11g Memory Structure

Oracle has many different memory structures for the various parts of the software's operation.

• System Global Area

Shared memory structures for data and sql. It is shared by both background processes and server processes. SGA is Oracle's structural memory area that facilitates the transfer of data and information between clients and the Oracle database. The size of the SGA is controlled by the DB_CACHE_SIZE parameter. Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

show parameter memory_target

• Shared Pool -
• Library Cache - A library of ready-to-go SQL statements.
• Dictionary Cache - For storing the most recently used data definitions in the Oracle DB. These data definitions may include information about: database files, tables, indexes, privileges, users, etc.
• Quickest Result Cache
• SQL Result Cache - Stores sql query result. Oracle skips execution next time.
• PLSQL Function Result Cache - Cache contains the result from a previous call to the function with the same parameter values, the system returns the cached result to the invoker and does not re-execute the function body.
• Database Buffer Cache - 
Largest portion of SGA. To cache data in memory for quicker access. Buffer refers to database block. A block in buffer cache could be in any of the three states: Free, Pinned and Dirty.
• Redo Log Buffer - 
Buffer that records every sql statement that changes data. The statement itself and any information required to reconstruct it is called Redo Entry. This buffer protects against loss of dirty blocks.
• Large Pool -
Optional component. It relieves shared pool of sometimes-transient memory requirements. Features using the Large Pool - Oracle Recovery Manager, Oracle Shared Server, Parallel Processing, I/O-related server processes.Without large pool configured, these processes share the memory from shared pool's SQL area.
• Java Pool - 
Optional component. This area handles the memory for Java methods, class definitions, etc.  The java_pool_size  parameter controls the amount of memory for this area.

Following are key SQL Queries related to SGA: 

• SHOW SGA
• SELECT * FROM v$sga;
• select * from v$sgainfo;
• select * from v$sga_dynamic_components;
 

• Program Global Area

A program global area (PGA) is a memory region that 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.

When you start a user process, that process has a private RAM area, used for sorting SQL results and managing special joins called “hash” joins.

The PGA can be critical to performance, particularly if your application is doing a large number of sorts.  Sort operations occur if you use ORDER by and GROUP BY commands in your SQL statements.

• Private SQL Area -
• Variables that might be assigned values during SQL execution
• Work areas for processing specific SQL needs: sorting, hash-joins, bitmap operations
• Cursors
• Session memory -
• login information
• session variables and settings

Following are key SQL Queries related to PGA:

• select * from v$pgastat;
• select * from v$pga_target_advice order by pga_target_for_estimate;
• select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;