System Global Area
The System Global Area (SGA) is typically a very large memory area shared by all Oracle processes. It is divided into distinct areas such as the buffer cache, the shared pool, and the log buffer, whose sizes are specified in the database configuration file (pfile or spfile). The various Oracle processes coordinate their access to these areas by using an interprocess communication (IPC) mechanisms called latches.
Typically, the buffer cache is the largest portion of the SGA. For reasons of efficiency, copies of data blocks (block buffers) are cached in computer memory whenever possible. Whenever a foreground process needs a data block, it first checks the buffer cache, hoping to find the block there. If a block is not found in the cache, query processing has to be delayed until the foreground process retrieves the block from the storage disks. When the buffer cache fills up, the least-recently-used blocks are removed to make space for new requests. Various strategies can be attempted to improve the efficiency of the cache. A special keep pool can be created within the buffer cache to store data blocks from frequently used data tables, for example, lookup tables. A recycle pool can be created to store blocks that are rarely reused. Other techniques include partitioning and clusters—more information is provided in Chapter 7.
The shared pool is another large component of the SGA and has many uses. The best known use is caching query execution plans for potential reuse by the user who first submitted the query or by any another user who submits the identical query—this is done in an area called the shared SQL area. Another well-known use is caching of information from the data dictionary—this is done in an area within the shared pool called the dictionary cache.