Bookmark and Share
Oracle SGA
(Publish Date: 2008-9-18 5:51pm, Total Visits: 992, Today: 1, This Week: 1, This Month: 2)

The main components of the SGA are the database buffer cache, the shared pool, the redo log buffer, the Java pool, and the optional large pool.

1. The Database Buffer Cache

The database buffer cache refers to the memory buffers that the database can use to hold the data that the server process reads from the data files on disk in response to user's requests. The buffer cache is designed to hold the more frequently accessed data in the memory buffers, as the alternative is to read the data constantly from the slower disk storage. The buffer cache contains both original blocks from disk and changed blocks that may be written back to disk. The database buffer cache holds the most recently used data blocks.

The Buffer Cache Hit Ratio
Hit ratio = (1 - (physical reads)/(logical reads)) * 100

For OLTP systems with large numbers of users, the sizing of the database buffer cache is critical, as it increases response times by retaining needed data blocks in memory, thereby reducing time-consuming disk I/O.

2. The Shared Pool

Unlike the database buffer cache, which holds actual data blocks, the shared pool holds executable PL/SQL code and SQL statements, as well as information regarding the data dictionary tables. The data dictionary is a set of key tables that Oracle maintains, and it contains crucial metadata about the database tables, users, privileges, and so forth.

1) The Library Cache

stored the parse the SQL statements (check it for syntax, among other things) before executing it. Each time you issue a new SQL statement, Oracle first checks the library cache to see if there is an already parsed and ready-to-execute form of the statement in there.

if SQL statement is in the Library Cache -- soft parse
if not -- hard parse

You make library cache sizing decisions based on the statistics regarding the hit and miss ratios on the library cache.

2) The Data Dictionary Cache

The data dictionary cache component of the shared pool primarily contains object definitions, usernames, roles, privileges, and other such information.

3. The Redo Log Buffer

When a server process changes data in the data buffer cache (via an insert, a delete, or an update), it generates redo data, which is recorded in the redo log buffer. The log writer process is in charge of writing redo information from the log buffer in memory to the redo log files on disk.

=== The log writer process writes the contents of the redo log buffer to disk under any of the following circumstances:

:: The redo log buffer is one-third full.

:: Users commit a transaction.

:: The database buffer cache is running low on free space and needs to write changed data to the redo log. The database writer instructs the log writer process to make room for the new data by first flushing the log buffer's contents to disk.

The redo log buffer is a "circular" buffer

4. The Large Pool and the Java Pool

Oracle uses the large pool area mostly for accommodating the Recovery Manager (RMAN) operations. You set the value of this pool in the initialization file by using the parameter large_pool_size.

The Java pool is designed for databases that contain a lot of Java code, so the regular SGA doesn't have to be allocated to components using Java-based objects.