Oracle Concepts - 14 Memory Architecture

. Basic Memory Structures

1. System global area (SGA)
2. Program global area (PGA)
3. User Global Area (UGA)
4. Software code areas

Oracle Database Memory Structures

Oracle Database Memory Management

• Automatic memory management
• You specify the target size for instance memory
• oracle redistributing memory as needed between the SGA and the instance PGA.
• Automatic shared memory management
• You set a target size for the SGA 
• PGA
• You set a target size for the PGA (let oracle doing the details)
• You set a target size for the PGA (let oracle doing the details)
• you managing PGA work areas individually
• Manual memory management (enjoy your life & database details)
• you set many initialization parameters
• you manage components of the SGA and instance PGA individually

- DBCA > Automatic Memory Management is the default

1. User Global Area (UGA)

• UGA is session memory, 
• UGA stores 
• session variables > PL/SQL package variables
• OLAP page pool > dimensional object such as a cube.
• UGA ppersistent
• dedicated server connection > UGA stored in PGA
• shared server connections > UGA stored in SGA

2. Program global area (PGA)

a memory heap that contains session-dependent variables required by a dedicated or shared server process

2.1 Instance PGA

2.2 Cursor

a name or handle to a specific private SQL area

2.3 PGA Contents

• Private SQL Area
• Sort Area
• Hash Area
• Bitmap Merge Area

• SQL Work Area
• Session Memory
• Persistent Area
• Runtime Area

2.3.1 Private SQL area 

• holds information about a parsed SQL
• store bind variable values, query execution state information, and query execution work areas.
• managed by client process
• Private SQL areas number limited by the initialization parameter OPEN_CURSORS

- private SQL area is divided into the following areas:

• Run-time area, 
• contains query execution state information like number of rows retrieved in a full-table scan
• freed when the SQL statement is closed
• Persistent area,
• contains bind variable values
• freed only when the cursor is closed

2.3.2 SQL Work Areas

• private allocation of PGA memory used for memory-intensive operations
• database automatically tunes work area sizes by enable automatic PGA memory management 
• larger work areas can significantly improve performance of an operator

- SQL Work Area examples:

• sort area to sort a set of rows
• Hash join operator uses a hash area to build a hash table from its left input
• bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

- Differences in Memory Allocation Between Dedicated and Shared

Memory Area	Dedicated	Shared
Nature of session memory	Private	Shared
Location of the persistent area	PGA	SGA
Location of the run-time area for DML/DDL statements	PGA	PGA

3. System Global Area (SGA)

• a read/write memory area that, along with the Oracle background processes, make up a database instance
• server and background processes do not reside within the SGA, but exist in a separate memory space
• All SGA components except the redo log buffer allocate and deallocate space in units of contiguous memory called granules
• Granule size is platform-specific and is determined by total SGA

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> show user

USER is "SYS"

SQL> startup

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             348129976 bytes

Database Buffers          100663296 bytes

Redo Buffers                6008832 bytes

Database mounted.

Database opened.

SQL> select instance_name,status,database_status,version,archiver from v$instance;

INSTANCE_NAME    STATUS       DATABASE_STATUS   VERSION           ARCHIVE

---------------- ------------ ----------------- ----------------- -------

orcl             OPEN         ACTIVE            11.2.0.2.0        STOPPED

### view information about SGA components by query  V$SGASTAT 

SQL> desc v$sgastat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 POOL                                               VARCHAR2(12)
 NAME                                               VARCHAR2(26)
 BYTES                                              NUMBER

SQL> set pages 0
SQL> select * from v$sgastat;
             fixed_sga                     1344840
             buffer_cache                 88080384
             log_buffer                    6008832
             shared_io_pool               12582912
shared pool  v_inc_meter_info_problem          464
shared pool  dpslut_kfdsg                      256
shared pool  hot latch diagnostics              80
shared pool  vips_package_file                 924
shared pool  kgqbt_alloc_block                2048
shared pool  ENQUEUE STATS                   16800
shared pool  kkj jobq  wor                    4104
shared pool  sskgplib                         1344
shared pool  transaction                    428428

.....................................

- SGA components are the following:

1. Database Buffer Cache
2. Redo Log Buffer
3. Shared Pool
4. Large Pool
5. Java Pool
6. Streams Pool
7. Fixed SGA

3.1 Database Buffer Cache- buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data blocks

- database buffer cache (buffer cache) is the memory area that stores copies of data blocks read from data files

- Buffer cache used for:

• Optimize physical I/O
• Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk (used with SSD disks and solaris & OEL only)

- Buffer States

• Unused, never been used, easiest for the database to used
• Clean,  contains a read-consistent version, no need to be checkpointed (Clean)
• Dirty, not yet been written to disk, must checkpoint before using it

- Every buffer has an access mode:

• pinned, not age out of memory while a user session accesses it
• unpinned (free).

- Database uses Pointers to dirty and nondirty buffers exist on the same least recently used (LRU) list which contains

• cold buffer is one that has not been recently used. 
• hot buffer is frequently accessed and has been recently used

- Buffer Modes

• current mode get (db block get), is a retrieval of a block as it currently appears in the buffer cache
• consistent read get, retrieval may use undo data, database uses undo data to create a read-consistent version of this block (called a consistent read clone)

- Buffer I/O(logical I/O)
refers to reads and writes of buffers in the buffer cache

- Buffer Writes, DBW writes cold, dirty buffers to disk in cases:

• server process cannot find clean buffers for reading new blocks
• database must advance the checkpoint
• Tablespaces are changed to read-only status or taken offline.

- Buffer Reads, clean or unused buffers is low, the database must remove buffers from the buffer cache

• Flash cache disabled > clean buffer as needed > read it from magnetic disk
• Flash cache enabled > clean buffer to the flash cache > read it from the flash cache

- cache hit occurs if the database finds the buffer in memory

- Server process searches the buffer cache for the buffer search order is as follows

• whole buffer in the buffer cache(logical read)
• buffer header in the flash cache LRU list
• does not find the buffer in memory (a cache miss) then:
• Copies the block from a data file into memory (a physical read)
• Performs a logical read of the buffer that was read into memory

Buffer Search

- Buffer Pools, is a collection of buffers. The database buffer cache is divided into one or more buffer pools.

• default pool is the only buffer pool
• Keep pool, for blocks that were accessed frequently
• Recycle pool, for blocks that are used infrequently, saving space in the cache