The System Global Area facilitates the transfer of information between users and the database. In general the SGA holds the most commonly requested information and objects. The SGA is comprised of several memory components. These memory components are listed below.
The diagram below shows the SGA in relation to the rest of the Oracle database.
Initialisation parameters controlling the database buffers.
The way in which objects are used depends on the type and nature of that object. Some objects are small and static such as lookup tables and other objects are large, static, read-only selection tables such as in a data warehouse. As a result some objects should be retained in memory and some should not. The multiple buffer pools are used to allow for retention in memory of different types, of particularly, static information. The V$BUFFER_POOL monitors various buffer pool settings currently in use. The KEEP and RECYCLE buffer pools help to segment memory from the Default buffer pool. Note that in this respect that the Default buffer pool can become short of memory.
DB_BLOCK_BUFFERS = 2000 #the pool cache DB_BLOCK_LRU_LATCHES = 6 #default is CPU_COUNT / 2, max is CPU_COUNT BUFFER_POOL_KEEP = '100,2' #assign 100 blocks from the cache and 2 LRU latches BUFFER_POOL_RECYCLE = '50,1' #assign 50 blocks from the cache a 1 LRU latch
The Default buffer pool is effectively the database block buffer cache. The total size of the database block buffer cache (default buffer pool) is calculated as DB_BUFFER_BLOCKS * DB_BLOCK_SIZE.
The KEEP buffer pool contains what inj Oracle7 were cached static lookup database tables. Only blocks read in from a full table scan are put into the most recently used (MRU) end of the least recently used (LRU) list. The KEEP buffer pool provides a segment in the database block buffer cache dedicated to the loading of the blocks of cached tables. Use the BUFFER_POOL_KEEP parameter to reserve part of the database block buffer cache for the use of pinned data blocks. Note the example below showing the BUFFER_POOL_KEEP option in the storage clause. Note that this clause has a slightly different effect to that of the ALTER TABLE tablename CACHE where the ALTER TABLE command would pin the table into the general area of the database block buffer cache, ie. the Default buffer pool.
CREATE TABLE tablename ( id NUMBER NOT NULL; name VARCHAR2(32) NOT NULL ) STORAGE (INITIAL 1M, NEXT 1M, BUFFER_POOL_KEEP)
The RECYCLE buffer pool is the opposite of the KEEP buffer pool and is intended to be allocated for blocks which are used infrequently. Be aware that very large objects could cause multiple I/O searches if the RECYCLE buffer pool is sized too small. An example is shpow below.
CREATE TABLE tablename ( id NUMBER NOT NULL; name VARCHAR2(32) NOT NULL ) STORAGE (INITIAL 1M, NEXT 1M, BUFFER_POOL_RECYCLE)
The large pool was added in Oracle8 as a result of MTS to allow a single process to manage several user connections. The two main purposes of the large pool are as follows. The large pool is sized using the LARGE_POOL_SIZE and the LARGE_POOL_MIN_ALLOC parameters. Note that sizing of the large pool allocates additional memory to the SGA and thus increasing the possibility of disk page swapping.
In Oracle7 MTS allowed sharing of resources by multiple users thus increasing resource usage of the SGA as a result of increased space requirements of the UGA. The UGA is contained within the SGA. When enough users were logged the SGA could reduce shared pool memory space for the data dictionary cache and library cache with an obvious drastic decrease in performance. Thus creation of the large pool in Oracle8 to contain the UGA in MTS instances resolved this problem. The V$SGASTAT shows the settings of the shared global area.
The redo log files hold records used for recovery purposes in the case of failure. If the database crashes for instance, the redo log files will be used by the Oracle database to reconstruct the most recent version of the Oracle database and all its datafiles at the most recently available point. Five groups of redo log files are effective.
Other parts of the shared global area are set using the following initialisation parameters.
The SHARED_POOL_SIZE initialisation parameter controls the shared pool size and is set to between 10% and 20% of available memory. In heavily interactive or ad-hoc environments the shared pool size can be much larger.
The dictionary cache is an area of memory used to store the most recently used data dictionary elements. Data dictionary elements include user definitions, datafile names, segment names, extent locations, table descriptions and privelages. Basically the meta-data, ie. the definitional data or the data about the data. The SHARED_POOL_SIZE parameter controls the size of the Shared SQL Pool of which the dictionary cache is a part. The size of the dictionary cache is internally controlled by Oracle.
SQL and PL/SQL are stored in the library cache. The library cache stores information about statements which are run against the database and allows the sharing of commonly used SQL statements in the form of execution plan and parsed SQL statements previously run against the database.
The control structures include the latches and locks, character set data and if MTS is in use the request and response queues.
The context areas contain contextual SQL details for specific users such as temporary storage of user created cursors during the life of the respective cursors.
The UGA contains session specific information for each Oracle database logged in user. Each user area assigned within the UGA is private to each user.
The sort extent pool (SEP) is contained within the SGA. The SEP tracks extents either in the shared pool area, ie. the UGA in MTS instances and in the PGA user's process area in non-MTS instances. Thus the SEP is always in the SGA since it tracks sort extents but the actual sort extents themselves are in several areas.
The PGA memory is the memory area utilised by a single Oracle user process. The PGA memory area is not shareable between users but stores user specific variables, ie. the PGA contains user session variables or session information. Each user is assigned a PGA and in multi-threaded server systems a UGA aswell. MTS allows multiple user processes to share the same server process reducing database memory requirements for multiple users. The PGA can also be referred to as the stack and data areas. Stack space contains session variable values.
In a server-enabled database the GC_FILES_TO_LOCKS and the GC_DB_LOCKS parameters can control the largest section of the SGA in the parallel server configuration.