Oracle 11gR2 Architecture( Brief Discussion on Memory)

Two basic memory structures are associated with an instance:

1)      SGA(System Global Area) 2) PGA(Program Global Area)

SGA:

Group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Databaseinstance. The SGA is shared by all server and background processes. Examples ofdata stored in the SGA include cached data blocks and shared SQL areas.

a)      Database buffer cache: Caches blocks of data retrieved from the database.

b)      Large pool: Is the optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes

c)      Java pool:Is used for all session-specific Java code and data in the Java Virtual Machine (JVM)

d)      Streams pool: Is used by Oracle Streams to store information required by capture and apply processes.

e)      Redo log buffer:Theredo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. Redo entries contain 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.

Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk.

Shared Pool:

a)      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.

b)      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. Oracle Database accesses the data dictionary frequently during SQL statement parsing.

c)      Server result cache: 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.

A client result cache differs from the server result cache. A client cache is configured at the application level and is located in client memory, not in database memory.

d)       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. Allocation of memory from the shared pool is performed 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.

e)      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.

Evolution of Memory Management Features

Memory management has evolved with each database releases
 Oracle 8i

Oracle 8i the SGA infrastructure allowed for the sizing of the DB Block buffer cache,Shared Pool and Large Pool changes need a shutting down databases.Also the entire memory calculations are manual.

Oracle 9i

Oracle9i, the dynamic SGA infrastructure allowed for the sizing of the Buffer Cache, Shared Pool and the Large Pool without having to shutdown the database. Key features being

1.Dynamic Memory resizing

2.DB_CACHE_SIZE instead of DB_BLOCK_BUFFERS

3.DB_nK_CACHE_SIZE for multiple block sizes

4. PGA_AGGREGATE_TARGET Introduction of Automatic PGA Memory management

 Oracle Database 10g
  ASMM was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g

AMM is being introduced in 11g. This enables automatic tuning of  SGA and PGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Automatic Memory Management –SGA and  PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

1.1.3          Switching to Automatic Memory Management

a)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET

Make the values of pga_aggregate_target and sga_target.

b) On a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. We decide to set to 1gb

c)Change the parameter in initialization parameter file.

Use Server Parameter File:

ALTER SYSTEM SET MEMORY_MAX_TARGET = 1gb SCOPE = SPFILE;
ALTER SYSTEM SET MEMORY_TARGET = 1gb SCOPE = SPFILE;
ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

If you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.

If you remove the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET

Note:  MEMORY_MAX_TARGET since it is a static parameter, this cannot be changed Dynamically and Instance has to be bounced for modifying the value.

d)Shutdown and startup the database

1.1.4          Monitoring and Tuning Automatic Memory Management

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

SQL> select * from v$memory_target_advice order by memory_size;

You can also use V$MEMORY_RESIZE_OPS. 

PGA:

Memory regions that contain dataand control information for a server or background process. A

PGA is nonshared memory created by Oracle Database when a server or background process is started. Access to the PGA is exclusive to the server process. Each server process and background process has its own PGA.

a)      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 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.

b)       SQL work area: 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.

 

 
 Redo Log Buffer:

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. These redo entries contain commands to rebuild or redo the changes. These entries are stored in Redo Log buffer. Log writer process (LGWR) writes these redo entries to redo log files. Redo log buffer works in circular fashion. It means that it overwrites old entries. But before overwriting, old entries must be copies to redo log files. Usually Log writer process (LGWR) is fast enough to mange these issues. Log writer process (LGWR) writes redo entries after certain amount of time to ensure that free space is available for new redo entries. LGWR process writes

• When user performs commit
• After every three seconds
• When redo log buffer is 1/3 full.
• When DBWn process writes data to disk and redo entries of these data hasn’t been written yet.

When DBWn writes modified data from Database buffer cache to disk, corresponding redo entries must also be written to disk. In fact redo entries are written prior to writing actual entries. DBWn process checks for redo entries, it signal LGWR process if redo entries have not been written.

If database is running in archiving mode then archive log files are created from redo log files. These archive log files are used to in various database recovery techniques.

LOG_BUFFER initialization parameter is used to set the size Redo Log buffer.

LGWR and Commits

Oracle Database uses a fast commit mechanism to improve performance for committed transactions. When a user issues a COMMIT statement, the transaction is assigned a system change number (SCN). LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the commit SCN and transaction's redo entries.

The redo log buffer is circular. When LGWR writes redo entries from the redo log buffer to an online redo log file, server processes can copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the online redo log is heavy.

The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle Database returns a success code to the committing transaction although the data buffers have not yet been written to disk. The corresponding changes to data blocks are deferred until it is efficient for DBWnto write them to the data files.

When activity is high, LGWR can use group commits. For example, a user commits, causing LGWR to write the transaction's redo entries to disk. During this write other users commit. LGWR cannot write to disk to commit these transactions until its previous write completes. Upon completion, LGWR can write the list of redo entries of waiting transactions (not yet committed) in one operation. In this way, the database minimizes disk I/O and maximizes performance. If commits requests continue at a high rate, then every write by LGWR can contain multiple commit records.

Note: LGWR can write redo log entries to disk before a transaction commits. The redo entries become permanent only if the transaction later commits.

The redo log can consist of two parts: the online redo log and the archived redo log.
To avoid losing the database due to a single point of failure, Oracle can maintain multiple sets of online redo log files.

Assuming the database operates in ARCHIVELOG mode, Oracle will create an archived redo log at every checkpoint - these can be used to recover from a disk failure.

Archived redo logs should be backed up and deleted regularly.

Online redo logs should not be backed up.
The size of a redo log file directly influences checkpoint frequency and performance. Checkpoint frequency can vary widely according to system activity but two or three per hour is typical. To insure a reasonable frequency of log switches set ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 2400 SCOPE=BOTH;

What is Redolog?

Literally, re-do means to do it again.
There is always something changing (update, delete, insert) in your database.
Those changes are recorded by your database.
Each record regarding a change in your database is called a redo record or redolog.
These redologs are stored in files called redolog files.
This is an internal mechanism of Oracle. You can not disable it. Every database should have redolog files.


Why does Oracle need redologs?

What if your database crashes and you lose data?
With help of redolog files Oracle can know what has happened in the past and can re-apply the changes that it sees in redo records.
You can think of redologs as the history of database.
Every change that happened in the database is recorded there.
If one day you lose data, you can examine your redo records and recover your data.

Database Buffer Cache:

When a query is processed, the oracle server looks in the database buffer cache for any block it needs. If the block is not found in the database buffer cache the server process reads the block from the data file and place a copy in the database buffer cache. Database Buffer Cache consists of three independent sub-caches:

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

Using Multiple Buffer Pools:

The database administrator (DBA)may be able to improve the performance of the database buffer cache by creating multiple buffer pools. You assign objects to abuffer pool depending on how the objects are accessed.There are three buffer pools:

Keep:

This pool is used to retain objects in memorythat are likely to be reused. Keeping these objects in memory reduces I/O operations. Buffers are kept in this pool by ensuring that the pool is sized larger than the total size of the segments assigned to the pool. This means that buffers do not have to be aged out. The keep pool isconfigured by specifying a value for the

DB_KEEP_CACHE_SIZEparameter.

Recycle:

This pool is used for blocks in memory that have little chance of being reused. The recycle pool is sized smaller than the total size of the segments assigned to the pool. This means that blocks read into the pool will often have to age out a buffer.The recycle pool is configured by specifying a value for the DB_RECYCLE_CACHE_SIZEparameter.

Default:

This pool always exists. It is equivalent to the buffer cache of an instance without a keep pool or a recycle pool and is configured with the DB_CACHE_SIZEparameter.

Note:

The memory in the keep or recycle pool isnot a subset of the default buffer pool.

Buffer Cache is organized into two lists

Write List

Write list contains dirty buffers. These are the data blocks which contain modified data and needed to be written to datafiles.

Least Recent Used (LRU) List

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

When an Oracle Database process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle Database user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle Database user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

When Oracle buffer cache is full, oracle LRU (least recently used) algorithm  makes room for subsequent requests for new buffers by aging out the buffers that have been least recently accessed. This allows the Oracle Server to keep the most recently requested data buffers n the Buffer Cache while the less commonly used data buffers are flushed.

O boy I love this diagram. Now lets assume this is LRU list. On your left is the MRU (most recently used) side and on your right is LRU (least recently used).

When a request is made for data, the user’s Server Process copies the blocks (A, B, C) from disk into buffers at the beginning (Most Recently Used) of the LRU List. These buffers stay on the LRU List but move toward the  least recently used end as additional data blocks are read into memory and then eventually aged out.

FULL TABLE SCANS are not affected by this! block resulted from FTS will always be copied over to least recently used end so that the all the buffers in the cache are not affected!

Dirty Buffers:


When server process get the data block from disk to memory, both memory and disk are synced. Now when blocks in memory are changed possible due to any DML statement submitted by SQL user, then these blocks in memory get changed and  are now called as 'Dirty Buffers' .

Now dirty buffers can not be over written if oracle needs more blocks to be placed from disc into memory and there is not enough space until these dirty blocks are written to disk first.

The Dirty List:

The mechanism for managing these dirty buffers is called the Dirty List or Write List. Using a checkpoint queue, this list keeps track of all the blocks that have been modified by users through the DML statements, but have not yet been written to disk.


Database Writer (DBW0):

Dirty buffers are written to disk by the Oracle background process Database Writer (DBW0).

SUMMARY:


1. When server process doesn't find data already in db buffer cache, it goes to the data files and read the data from there.

2. Before the data is read from disk however, the free buffer should be there in db cache to hold that data into memory.

3. while making room for the data in memory, server process moves the dirty buffers from  LRU list to and Dirty List

4.DBW0 writer writes dirty buffers from dirty list to data files when it hits a pre-determined threshold. DBW0 process can also write directly from the LRU list to data files if it doesn't find any free buffers there.

The LRU Algorithm and Full Table Scans

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.

There are three type buffers: Dirty, Free, Pinned

Dirty buffer: Dirty buffers are the buffers blocks that need to be written to the data file.

Free buffer: Free buffer do not contain any data, when oracle reads data from disk free buffer holds the data.

Pinned buffer: Pinned buffer is that is being currently used.

Clean:

The buffer is now unpinned and is a candidatefor immediate aging out if the current contents (data block) are not referenced again. Either the contents are in sync with disk or the buffer contains a CR snapshot of a block.

Oracle Free Buffer Waits

The free buffer wait is an Oracle metric that has generally been considered to be related to the database writer (DBWR).  Generally, the free buffer wait event indicates that dirty blocks are not being cleared fast enough by the DBWR and some server process is reporting that it is unable to find a free buffer.  This results in a free buffer wait.

A dirty buffer is one whose contents have been modified.  Dirty buffers are freed for reuse once the DBWR has written the blocks to disk.  Once the dirty buffers are freed, the server process should complete without recording a free buffer wait; however, this is not always the case.

If a session needs to load a block from disk or make a consistent read clone of an existing buffer, it needs to be able to empty another buffer and re-use it.

But if all the buffers are either dirty or pinned the session has to wait for a buffer to become available – either when the database writer cleans some buffers (by copying them to disc) or when some pinned buffers are finally released.  Given the size of buffer caches these days, and the limit on how many buffers a single session may pin, the “free buffer waits” is more likely to be a database writer problem than anything else.

There are less common, but valid, reasons for free buffer waits.  Depending on the size of the buffer cache or the number of buffers that can be pinned for each session, it is possible for sessions to encounter free buffer waits for other reasons. 

Sometimes a session is not able to find a free buffer due to the amount of un-released pinned  buffers.  This particular source of a free buffer wait is far less common that those caused by free buffer waits caused by the DBWR.

As you can see below, other Oracle experts have expounded on the following:

• free buffer waits caused by "unselective SQL" flooding the buffer cache with index blocks

• free buffer waits caused by bandwidth limitations

Rich Niemiec notes details on Oracle free buffer waits where a non-selective index causes free buffer waits:

“Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process.”

Steve Adams says it best about Free Buffer waits, where he suggests that free buffer waits as a database writer bandwidth issue, since it's the job of DBWR to make free buffers available, and a bandwidth issue will precipitate free buffer waits.

“Free buffer waits should be regarded as a DBWn issue.

It is not enough to just investigate what the session waiting on this event was trying to do.

Yes, it might be the "query from hell", but DBWn's job is to make free buffers available, no matter what.

To tune DBWn, you need to be aware of the write bandwidth available at the hardware, at the operating system level, and at the Oracle level.

If the bandwidth is inadequate at any point, you will see free buffer waits.

If the bandwidth is lower at the operating system or hardware level than at the Oracle level, then you will see substantial write complete waits.

It is clear that the standard assumption that free buffer waits are caused by the DBWR's inability to keep up much less get ahead of the session demand is likely the best one; however, a little investigation into the source of the free buffer waits may send you in a different direction.