Virtual memory simulates memory using a combination of real (main) memory and secondary storage (usually disk space). The operating system accesses virtual memory by making secondary storage look like main memory to application programs.
Note: Usually, it is best to keep the entire SGA in real memory.
User programs can be shared or non-shared. Some Oracle tools and utilities, such as SQL*Forms and SQL*Plus, can be installed shared, but some cannot. Multiple instances of Oracle can use the same Oracle code area with different databases if running on the same computer.
Additional Information: Installing software shared is not an option for all operating systems; for example, it is not on PCs operating MS DOS. See your Oracle operating system-specific documentation for more information.
As described in "An Oracle Instance" ![[*]](jump.gif)
, an SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance and the memory is reclaimed when you shut down the instance. Each instance has its own SGA.
The SGA contains the following subdivisions:
With Release 7.3, the buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.
Organization of the Buffer Cache The buffers in the cache are organized in two lists: the dirty list and the least-recently-used (LRU) list. The dirty list holds dirty buffers. A dirty buffer is a buffer that has been modified but has not yet been written to disk. The least-recently-used (LRU) list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the dirty list. Free buffers are buffers that have not been modified and are available for use. Pinned buffers are buffers that are currently being accessed.
When an Oracle 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" towards the LRU end of the LRU list.
When a user process needs to access a block that is not already in the buffer cache, the process must read the block from a datafile on disk into a buffer in the cache. Before reading a 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.
As a user process searches the LRU list, it may find dirty buffers. If the user process finds a dirty buffer, it moves the buffer to the dirty list and continues to search. When a user process finds a free buffer, it reads the block into the buffer and moves it to the MRU end of the LRU list.
If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBWR process to write some of the dirty buffers to disk. See "Database Writer (DBWR)" for more information about the DBWR background process.
Size of the Buffer Cache The initialization parameter DB_BLOCK_BUFFERS specifies the number of buffers in the database buffer cache. Each buffer in the cache is the size of one Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE); therefore, each database buffer in the cache can hold a single data block read from a datafile.
The first time an Oracle user process accesses a piece of data, the process must copy the data from disk to the cache before accessing it. This is called a cache miss. When a process accesses a piece of data that is already in the cache, the process can read the data directly from memory. This is called a cache hit. Accessing data through a cache hit is faster than data access through a cache miss.
Since the cache has a limited size, all the data on disk cannot fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write data already in the cache to disk to make room for the new data. Subsequent access to the data written to disk results in a cache miss.
The size of the cache affects the likelihood that a request for data will result in a cache hit. If the cache is large, it is more likely to contain the data that is requested. Increasing the size of a cache increases the percentage of data requests that result in cache hits.
You can prevent the 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 on the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You may want to specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
Redo entries are copied by Oracle server processes from the user's memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active online redo log file group on disk. See "Log Writer (LGWR)" for more information about how the redo log buffer is written to disk.
Size of the Redo Log Buffer The initialization parameter LOG_BUFFER determines the size (in bytes) of redo log buffer. In general, larger values reduce log file I/O, particularly if transactions are long or numerous. The default setting is four times the maximum data block size for the host operating system.
Figure 9 - 5. Contents of the Shared Pool
Library Cache The library cache includes shared SQL areas, private SQL areas, PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL Areas and Private SQL Areas Oracle represents each SQL statement it executes with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the same shared part for those users. However, each user must have a separate copy of the statement's private SQL area.
A shared SQL area is a memory area that contains the parse tree and execution plan for a single SQL statement. Oracle allocates memory from the shared pool when a SQL statements is parsed and the size of this memory depends on the complexity of the statement. A shared SQL area is always in the shared pool and is shared for identical SQL statements. For more information about the criteria used to determine identical SQL statements, see "Shared SQL" .
A private SQL area is a memory area that contains data such as bind information and runtime buffers. Each session that issues a SQL statement has a private SQL area. Each user that submits an identical SQL statement has his/her own private SQL area that uses a single shared SQL area; many private SQL areas can be associated with the same shared SQL area.
A private SQL area has a persistent area and a runtime area:
For selects processing large amounts of data where sorts are needed, application developers should cancel the query if the client is satisfied with a partial result of a fetch. For example, in an Oracle Office application, a user can select from a list of over sixty templates for creating a mail message. Oracle Office displays the first ten template names and the user chooses one of these templates. The application can continue to try to display more template names, but because the user has chosen a template, the application should cancel the processing of the rest of the query.
The location of a private SQL area varies depending on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multi-threaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA.
How the User Process Manages Private SQL Areas The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50.
How Oracle Manages Shared SQL Areas Since shared SQL areas must be available to multiple users, the library cache is contained in the shared pool within the SGA. The size of the library cache, along with the size of the data dictionary cache, is limited by the size of the shared pool. Memory allocation for shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value for this parameter is 3.5 megabytes.
If a user process tries to allocate a shared SQL area after the entire shared pool has been allocated, Oracle can deallocate items from the pool using a modified least-recently-used algorithm until there is enough free space for the new item. If a shared SQL area is deallocated, the associated SQL statement must be reparsed and reassigned to another shared SQL area when it is next executed.
PL/SQL Program Units and the Shared Pool Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) similar to processing individual SQL statements. Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that executes the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user executes the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his/her private SQL area, holding values specific to his/her session.
Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that executes the statement.
Dictionary Cache The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Among the data stored in the data dictionary are the following:
Since the data dictionary is accessed so often by Oracle, two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also know as the row cache. The other area in memory to hold dictionary data is in the library cache. The data dictionary caches are shared by all Oracle user processes.
When a SQL statement is submitted to Oracle for execution, there are some special steps to consider. On behalf of every SQL statement, Oracle automatically performs the following memory allocation steps:
If you change a database's global database name, all information is flushed from the shared pool. If desired, the administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance.
Cursors The application developer of an Oracle Precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Each user session can open any number of cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, the database administrator can alter the OPEN_CURSORS initialization parameters. For more information about cursors, see "Cursors" .
Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor may execute several recursive calls. These recursive cursors also utilize shared SQL areas.
, and "Dispatcher Request and Response Queues" .)
The primary determinants of the size of the SGA are the parameters found in a database's parameter file. The parameters that most affect SGA size are the following:
Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here.
for more information about sessions. The contents of a PGA vary, depending on whether the associated instance is running the multi-threaded server. Figure 9 - 6 summarizes these differences.
Figure 9 - 6. The Contents of a PGA with and without the Multi-Threaded Server
Stack Space A PGA always contains a stack space, which is memory allocated to hold a session's variables, arrays, and other information.
Session Information A PGA in an instance running without the multi-threaded server requires additional memory for the user's session, such as private SQL areas and other information. If the instance is running the multi-threaded server, this extra memory is not in the PGA, but is instead allocated in the SGA.
Shared SQL Areas Shared SQL areas are always in shared memory areas of the SGA (not the PGA), with or without the multi-threaded server.
The following initialization parameters affect the sizes of PGAs:
The size of the stack space in each PGA created on behalf of Oracle background processes (such as DBWR and LGWR) is affected by some additional parameters. See your Oracle operating system-specific documentation for more information.
If the amount of data to be sorted does not fit into a sort area, then the data is divided into smaller pieces that do fit. Each piece is then sorted individually. The individual sorted pieces are called "runs". After sorting all the runs, Oracle merges them to produce the final result.
If memory and temporary space are abundant on your system and you perform many large sorts to disk, the setting of the initialization parameter SORT_DIRECT_WRITES can increase sort performance.
For Release 7.3 and greater, the default value of SORT_DIRECT_WRITES is AUTO. If the initialization parameter is unspecified or set to AUTO, the database automatically allocates direct write buffers if the SORT_AREA_SIZE is ten times the minimum direct write buffer configuration. In this case, the sort allocates the direct write buffers out of a portion of the total sort area, ignoring the settings of SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS.
If you set SORT_DIRECT_WRITES to FALSE, the sorts that write to disk will write through the buffer cache. If you set the parameter to TRUE, each sort allocates additional buffers in memory for direct writes. You can set the initialization parameters SORT_WRITE_BUFFERS and SOFT_WRITE_BUFFER_SIZE to control the number and size of these buffers. The sort writes an entire buffer for each I/O operation. The Oracle process performing the sort writes the sort data directly to the disk, bypassing the buffer cache.