Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter discusses the memory architecture of an Oracle instance. It includes:
Oracle uses memory to store information such as the following:
The basic memory structures associated with Oracle include:
Figure 8-1 illustrates the relationships among these memory structures.
Software Code Areas are another basic memory structure, discussed .
A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:
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. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA.
See Also:
|
Beginning with Oracle9i, Release 1 (9.0.1), Oracle can change its SGA configuration while the instance is running. With the dynamic SGA infrastructure, the sizes of the buffer cache, the shared pool, and the large pool can be changed without shutting down the instance.
Dynamic SGA also allows Oracle to set, at run time, limits on how much virtual memory Oracle will use for the SGA. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE.
If SGA_MAX_SIZE
specified in INIT.ORA is less than the sum of all components specified or defaulted at initialization time, then the setting of SGA_MAX_SIZE
in INIT.ORA is ignored.
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and virtual memory is used to store parts of it, then overall database system performance can decrease dramatically because portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.
The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:
The memory allocated for an instance's SGA is displayed on instance startup when using Oracle Enterprise Manager (or SQL*Plus). You can also display the current instance's SGA size by using the SQL*Plus SHOW
statement with the SGA
clause.
See Also:
|
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.
The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.
The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers 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 toward the LRU end of the LRU list.
The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.
Before reading a data 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.
If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer 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 DBW0 background process to write some of the dirty buffers to disk.
When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.
You can control this 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 at the MRU end of the list during a full table scan, use the CACHE
clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
Oracle9i, Release 1 (9.0.1), supports multiple block size in a database. This is the default block size--the block size used for the system tablespace. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE.
Legitimate values are from 2K to 32K.
To specify the size of the standard block size cache, you set the initialization parameter DB_CACHE_SIZE.
Optionally, you can also set the size for two additional buffer pools, KEEP
and RECYCLE
,
by setting DB_KEEP_CACHE_SIZE
and DB_RECYCLE_CACHE_SIZE.
These three parameters are independent of one another in Oracle9i, Release 1 (9.0.1).
The sizes and numbers of non-standard block size buffers are specified by the following parameters:
Each parameter specifies the size of the cache for the corresponding block size.
db_block_size=4096 db_cache_size=1024M db_2k_cache_size=256M db_8k_cache_size=512M
In the above example, the parameter DB_BLOCK_SIZE
sets the standard block size of the database to 4K. The size of the cache of standard block size buffers will be 1024M. Additionally, 2K and 8K caches are also configured, with sizes of 256M and 512M, respectively.
The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle to write dirty data already in the cache to disk to make room for the new data. (If a buffer is not dirty, it does not need to be written to disk before a new block can be read into the buffer.) Subsequent access to any data that was written to disk results in additional cache misses.
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.
Oracle9i, Release 1 (9.0.1), lets you change the size of the buffer cache while the instance is running, without having to shut down the database. You do this with the ALTER SYSTEM
command. For more information, see "Control of the SGA's Use of Memory".
You can use the fixed view V$BUFFER_CACHE
to track the sizes of the different cache components and any pending resize operations.
The parameter DB_BLOCK_LRU_LATCHES
no longer needs to be set in Oracle9i, Release 1 (9.0.1). Its value has been obsoleted and is now computed automatically.
See Also:
Oracle9i Database Performance Guide and Reference for information about tuning the buffer cache |
You can configure the database buffer cache with 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. Particular schema objects (tables, clusters, indexes, and partitions) can then be assigned to the appropriate buffer pool to control the way their data blocks age out of the cache.
KEEP
buffer pool retains the schema object's data blocks in memory.
RECYCLE
buffer pool eliminates data blocks from memory as soon as they are no longer needed.
DEFAULT
buffer pool contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT
pool.
The initialization parameters that configure the KEEP
and RECYCLE
buffer pools are BUFFER_POOL_KEEP
and BUFFER_POOL_RECYCLE.
See Also:
|
The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER,
or DROP
operations. Redo entries are used for database recovery, if necessary.
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 (or group of files) on disk.
See Also:
|
The initialization parameter LOG_BUFFER
determines the size (in bytes) of the 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.
The shared pool portion of the SGA contains three major areas: library cache, dictionary cache, buffers for parallel execution messages, and control structures.
The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.
The default value of this parameter is 8M on 32-bit platforms and 64M on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.
The library cache includes the shared SQL areas, private SQL areas (in the case of a multiple transaction server), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.
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 shared SQL area for those users. However, each user must have a separate copy of the statement's private SQL area.
A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements executed multiple times, which often happens when many users execute the same application.
Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area. If Oracle deallocates a shared SQL area, the associated SQL statement must be reparsed and reassigned to another shared SQL area at its next execution.
Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes 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 or her private SQL area, holding values specific to his or 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.
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.
In general, any item (shared SQL area or dictionary row) in the shared pool remains until it is flushed according to a modified LRU algorithm. The memory for items that are not being used regularly is freed if space is required for new items that must be allocated some space in the shared pool. A modified LRU algorithm allows shared pool items that are used by many sessions to remain in memory as long as they are useful, even if the process that originally created the item terminates. As a result, the overhead and processing of SQL statements associated with a multiuser Oracle system is minimized.
When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:
Oracle also flushes a shared SQL area from the shared pool in these circumstances:
ANALYZE
statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is executed, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.
ALTER SYSTEM FLUSH SHARED_POOL
is used to do this.
See Also:
ALTER SYSTEM FLUSH SHARED_POOL
V$SQL
and
dynamic views
V$SQLAREA
The database administrator can configure an optional memory area called the large pool to provide large memory allocations for:
PARALLEL_AUTOMATIC_TUNING
is set to TRUE
(otherwise, these buffers are allocated to the shared pool)
By allocating session memory from the large pool for shared server, Oracle XA, or parallel query buffers, Oracle can use the shared pool primarily for caching shared SQL and avoid the performance overhead caused by shrinking the shared SQL cache.
In addition, the memory for Oracle backup and restore operations, for I/O server processes, and for parallel buffers is allocated in buffers of a few hundred kilobytes. The large pool is better able to satisfy such large memory requests than the shared pool.
The large pool does not have an LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
See Also:
|
Dynamic SGA provides external controls for increasing and decreasing Oracle's use of physical memory. Together with the dynamic buffer cache, shared pool, and large pool, dynamic SGA allows the following:
SGA_MAX_SIZE
specification.
You can use several initialization parameters to control how the SGA uses memory.
The LOCK_SGA
parameter locks the SGA into physical memory.
The SHARED_MEMORY_ADDRESS
and HI_SHARED_MEMORY_ADDRESS
parameters specify the SGA's starting address at runtime. These parameters are rarely used. They are needed only on platforms that do not specify the SGA's starting address at link time. For 64-bit platforms, HI_SHARED_MEMORY_ADDRESS
specifies the high order 32 bits of the 64-bit address.
The USE_INDIRECT_DATA_BUFFERS
parameter enables the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory.
However, the dynamic buffer cache feature in Oracle9i, Release 1 (9.0.1), requires every buffer to have a valid virtual address. This is because the underlying unit of allocation, a granule, is identified by its virtual address. For this reason, the extended cache feature will be obsolete in future releases of Oracle9i.
See Also:
|
A program global area (PGA) is a memory region which contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.
The content of the PGA memory varies, depending on whether the instance is running the shared server option or not. But generally speaking, the PGA memory can be classified as follows:
A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.
The private SQL area of a cursor is itself divided into two areas whose lifetimes are different:
Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE,
and DELETE
statements, Oracle frees the runtime area after the statement has been executed. For queries, Oracle frees the runtime area only after all rows are fetched or the query is canceled.
The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process's PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
See Also:
|
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. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared 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.
A private SQL area continues to exist until the corresponding cursor is closed or the statement handle is freed. Although Oracle frees the runtime area after the statement completes, the persistent area remains waiting. Application developers close all open cursors that will not be used again to free the persistent area and to minimize the amount of memory required for users of the application.
Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
For complex queries (for example, decision-support queries), a big portion of the runtime area is dedicated to work areas allocated by memory-intensive operators such as the following:
For example, a sort operator uses a work area (sometimes called the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (also called the hash area) to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, the input data is divided into smaller pieces. This allows some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators will run faster with larger work area.
The size of a work area can be controlled and tuned. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Optimally, the size of a work area is big enough such to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time will increase, because part of the input data must be spilled to temporary disk storage. In the extreme case, if the size of a work area is far too small compared to the input data size, multiple passes over the data pieces must be performed. This can dramatically increase the response time of the operator.
In Oracle 8i and prior releases, the DBA could control the maximum size of SQL work areas by setting the following parameters: sort_area_size, hash_area_size, bitmap_merge_area_size
and create_bitmap_area_size.
Setting these parameters is difficult, because the maximum work area size is ideally selected on the basis of the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, the various *_area_size
parameters are hard to tune under the best of circumstances.
Oracle9i, Release 1 (9.0.1), introduces a new mode to automatically and globally manage the size of SQL work areas. To enable this mode, the DBA simply needs to specify the total size dedicated to PGA memory for the Oracle instance. This is done be setting the new initialization parameter pga_aggregate_target.
The specified number (for example, 2G) is a global target for the Oracle instance and Oracle will try to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.
When running in this mode, sizing of work areas for all dedicated sessions becomes automatic and all *_area_size
parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter pga_aggregate_target.
Simply speaking, this amount is set to the value of pga_aggregate_target
minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then allotted to individual active work areas on the basis of their specific memory requirement.
In Oracle9i, Release 1 (9.0.1), new V$
views and columns have been added to provide PGA memory usage statistics. Most of these statistics are enabled when pga_aggregate_target
is set.
V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$PROCESS
view to report the PGA memory allocated and used by an Oracle process:
PGA_USED_MEM
PGA_ALLOCATED_MEM
PGA_MAX_MEM
See Also:
|
Memory allocation depends, in some specifics, on whether the system uses dedicated or shared server architecture. Table 8-1 shows the differences.
Software code areas are portions of memory used to store code that is being executed or can be executed. Oracle code is stored in a software area that is typically at a different location from users' programs--a more exclusive or protected location.
Software areas are usually static in size, changing only when software is updated or reinstalled. The required size of these areas varies by operating system.
Software areas are read-only and can be installed shared or nonshared. When possible, Oracle code is shared so that all Oracle users can access it without having multiple copies in memory. This results in a saving of real main memory and improves overall performance.
User programs can be shared or nonshared. 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.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|