Oracle Architecture SGA

 Architecture:

 

Basic Memory Structures

Oracle Database includes several memory areas, each of which contains multiple subcomponents. The basic memory structures associated with Oracle Database include:

·         System global area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

·         Program global area (PGA)

A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

·         User global area (UGA): The UGA is memory associated with a user session.

 

Oracle Database Memory Management

Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change. Oracle Database manages memory based on the settings of memory-related initialization parameters.

The basic options for memory management are as follows:

·         Automatic memory management

You specify the target size for the database instance memory. The instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.

·         Automatic shared memory management

This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.

·         Manual memory management

Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.

Note: If you create a database with Database Configuration Assistant (DBCA) and choose the basic installation option, then automatic memory management is the default.

SGA and PGA are the main parts of memory architecture .Background process are used by memory structures to work on CRD  and other database files .

SGA 

SGA is shared global area or system global area is the memory are that controls and manipulates the Data processing in Database. All Background and server process are shares SGA.   

The SGA is a read/write memory area that, along with the Oracle background processes, form a database instance.

Instance = SGA + Mandatory Background Process

 All SGA components except the redo log buffer allocate and de allocate space in units of contiguous memory called granules. Granule size is platform-specific and is determined by total SGA size.

 

The most important SGA components are the following:

·         Database Buffer Cache

The database buffer cache, also called the buffer cache, is the memory area that stores copies of    data blocks read from data files (Storage).

A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache

Buffer States

    The database uses internal algorithms to manage buffers in the cache.

    A buffer can be in any of the following mutually exclusive states:

·         Unused

The buffer is available for use because it has never been used or is currently unused. This type of buffer is the easiest for the database to use.

·         Clean

This buffer was used earlier and now contains a read-consistent version of a block as of a point in time. The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.

·         Dirty

The buffer contains modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.

Every buffer has an access mode: pinned or free (unpinned). A buffer is "pinned" in the cache so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.

Buffer Modes

When a client requests data, Oracle Database retrieves buffers from the database buffer cache in either current mode or consistent mode.

·         Current mode

A current mode get, also called a db block get, is a retrieval of a block as it currently appears in the buffer cache. For example, if an uncommitted transaction has updated two rows in a block, then a current mode get retrieves the block with these uncommitted rows. The database uses db block gets most frequently during modification statements, which must update only the current version of the block.

·         Consistent mode

A consistent read get is a retrieval of a read-consistent version of a block. This retrieval may use undo data. For example, if an uncommitted transaction has updated two rows in a block, and if a query in a separate session requests the block, then the database uses undo data to create a read-consistent version of this block (called a consistent read clone) that does not include the uncommitted updates. Typically, a query retrieves blocks in consistent mode.

·         Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that stores redo entries describing  changes  made to the database.

A redo record is a data structure that contains the information necessary to reconstruct, or   redo, changes made to the database by DML or DDL operations. Database recovery applies  redo entries to data files to reconstruct lost changes.

·         Shared Pool

The shared pool caches various types of program data.

The shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database.

 

Library Cache

The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code.

This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas.

When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as a hard parse or a library cache miss.

Shared SQL Areas

The database represents each SQL statement that it runs in the shared SQL area and private SQL area.

The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement. Each session issuing a SQL statement has a private SQL area in its PGA. Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.

Data Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. The cache is also known as the row cache because it holds data as rows instead of buffers.

Oracle Database accesses the data dictionary frequently during SQL statement parsing. The data dictionary is accessed so often by Oracle Database

Server Result Cache

The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks.

The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.

If the results are cached, then the database returns them immediately. In this way, the database avoids the expensive operation of rereading blocks and recomputing results.

The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of database objects used to construct that cached result.

Reserved Pool

The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.

The database allocates memory from the shared pool in chunks. Chunking allows large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of contiguous memory because of fragmentation.

·         Large Pool

The large pool is an optional memory area intended for memory allocations that are   larger than   is appropriate for the shared pool. The large pool can provide large memory allocations for the following:

UGA for the shared server and the Oracle XA interface (used where transactions  interact with multiple databases)

          Message buffers used in parallel execution

          Buffers for Recovery Manager (RMAN) I/O slaves   

The large pool does not have an LRU list. When the database allocates large pool memory to a database session, this memory is not eligible to be released unless the session releases it. As soon as a portion of memory is freed, other processes can use it. By allocating session memory from the large pool, the database avoids the fragmentation that can occur in the shared pool.

• Java Pool

The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM). This memory includes Java objects that are migrated to the Java session space at end-of-call.

·         In-Memory Area

The In-Memory Area is an optional SGA component that contains the In-Memory Column Store (IM column store).

The IM column store contains copies of tables, partitions, and materialized views in a columnar format optimized for rapid scans. The IM column store supplements the database buffer cache, which stores data in traditional row format.

• Fixed SGA

The fixed SGA is an internal housekeeping area.

For example, the fixed SGA contains:

·         General information about the state of the database and the instance, which the background processes need to access

·         Information communicated between processes, such as information about locks

The size of the fixed SGA is set by Oracle Database and cannot be altered manually. The fixed SGA size can change from release to release.

• shared I/O pool 
• Streams Pool

·         Database Smart Flash Cache 

 

PGA

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.

The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA.

 

Private SQL Area

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing.

When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

A cursor is a name or handle to a specific private SQL area

A private SQL area is divided into the following areas:

·         The run-time area

This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.

Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.

·         The persistent area

This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.

The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter OPEN_CURSORS.

SQL Work Areas

A work area is a private allocation of PGA memory used for memory-intensive operations.

For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

Data Concurrency

data concurrency which is the simultaneous access of the same data by multiple users.

Oracle Database uses locks to control concurrent access to data. A lock is a mechanism that prevents destructive interaction between transactions accessing a shared resource. Locks help ensure data integrity while allowing maximum concurrent access to data.

Data Consistency

each user must see a consistent view of the data, including visible changes made by a user's own transactions and committed transactions of other users.

SYS and SYSTEM Schemas

All Oracle databases include default administrative accounts.

Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

The SYS administrative account is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user.

The SYSTEM administrative account is also automatically created when a database is created. The SYSTEM schema stores additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.