The SGA is a read/write memory area that, along with the Oracle background processes, makes up a database instance. All server processes that execute on behalf of users can read information in the instance SGA. Several processes write to the SGA during database operation.
Each database instance has its own SGA. Oracle Database automatically allocates memory for an SGA at instance startup and reclaims the memory at instance shutdown.
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. 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. Oracle Database uses the buffer cache to achieve the following goals:
■Optimize physical I/O
The database updates data blocks in the cache and stores metadata about the changes in the redo log buffer. After a COMMIT, the database writes the redo buffers to disk but does not immediately write data blocks to disk. Instead, database writer (DBWn) performs lazy writes in the background.
■Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk
The database uses internal algorithms to manage buffers in the cache. A buffer can be any of the following mutually exclusive states:
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.
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 check pointed. The database can pin the block and reuse it.
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. The database uses a sophisticated algorithm to make buffer access efficient. Pointers to dirty and non dirty buffers exist on the same least recently used (LRU) list, which has a hot end and cold end. A cold buffer is one that has not been recently used. A hot buffer is frequently accessed and has been recently used.
Note: Database Smart Flash Cache is available only in Solaris and Oracle Enterprise Linux.
Note: Conceptually, there is only one LRU, but for concurrency the database actually uses several LRUs.
A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools. You can manually configure separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. You can then assign specific schema objects to the appropriate buffer pool to control how blocks age out of the cache.
The possible buffer pools are as follows:
This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.
This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.
This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.
A database has a standard block size. You can create a table space with a block size that differs from the standard size. Each non default block size has its own pool. Oracle Database manages the blocks in these pools in the same way as in the default pool.
Redo Log Buffer
The redo 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.
The shared pool caches various types of program data. For example, 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. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool. The shared pool is divided into several sub components, the most important of which are
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 following SQL areas:
■Shared 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.
■Private SQL area
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. Oracle Database accesses the data dictionary frequently during SQL statement parsing. The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data:
■Data dictionary cache
This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers.
All server processes share these caches for access to data dictionary information.