Oracle7 Server Concepts Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Memory Structures and Processes

Yea, from the table of my memory I'll wipe away all trivial fond records.

Shakespeare: Hamlet

This chapter discusses the memory structures and processes in an Oracle database system. It includes:

An Oracle Instance

Regardless of the type of computer executing Oracle and the particular memory and process options being used, every running Oracle database is associated with an Oracle instance. Every time a database is started on a database server, Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. The combination of the SGA and the Oracle processes is called an Oracle database instance. The memory and processes of an instance work to manage the database's data efficiently and serve the one or multiple users of the associated database. Figure 9 - 1 shows an Oracle instance.

Figure 9 - 1. An Oracle Instance

Oracle starts an instance, then mounts a database to the instance. Multiple instances can execute concurrently on the same machine, each accessing its own physical database. In clustered and massively parallel systems, the Oracle Parallel Server allows a single database to mount multiple instances. When you use Trusted Oracle in OS MAC mode, a single instance can mount multiple databases.

The remaining sections of this chapter explain the memory and process structures and configurations associated with an Oracle instance. See Oracle7 Parallel Server Concepts & Administration for more information about the Oracle Parallel Server. See the Trusted Oracle7 Server Administrator's Guide for more information about Trusted Oracle.

Process Structure

A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

The process structure of Oracle is important because it defines how multiple activities can occur and how they are accomplished. For example, two goals of a process structure might be

The Oracle's process architecture is designed to maximize performance.

Single-Process Oracle Instance

Single-process Oracle (also called single-user Oracle) is a database system in which all Oracle code is executed by one process. Different processes are not used to separate execution of the parts of Oracle and the client application program. Instead, all code of Oracle and the single user's database application is executed by a single process.

Figure 9 - 2 shows a single-process Oracle instance. The single process executes all code associated with the database application and Oracle.

Figure 9 - 2. A Single-Process Oracle Instance

Only one user can access an Oracle instance in a single-process environment; multiple users cannot access the database concurrently. For example, Oracle running under the MS-DOS operating system on a PC can only be accessed by a single user because MS-DOS is not capable of running multiple processes.

Multiple-Process Oracle Instance

Multiple-process Oracle (also called multi-user Oracle) uses several processes to execute different parts of Oracle, and a separate process for each connected user. Each process in a multiple-process Oracle instance performs a specific job. By dividing the work of Oracle and database applications into several processes, multiple users and applications can simultaneously connect to a single database instance while the system maintains excellent performance. Most database systems are multi-user, because one of the primary benefits of a database is managing data needed by multiple users at the same time.

Figure 9 - 3 illustrates a multiple-process Oracle instance. Each connected user has a separate user process and several background processes are used to execute Oracle. This figure might represent multiple concurrent users running an application on the same machine as Oracle; this particular configuration is usually on a mainframe or minicomputer.

Figure 9 - 3. A Multiple-Process Oracle Instance

In a multiple-process system, processes can be categorized into two groups: user processes and Oracle processes. The following sections explain these classes of processes.

User Processes

When a user runs an application program, such as a Pro*C program, or an Oracle tool, such as Server Manager, Oracle creates a user process to run the user's application.

Oracle Processes

In multiple-process systems, two types of processes control Oracle: server processes and background processes.

Oracle creates server processes to handle the requests of user processes connected to the instance. Often, when the application and Oracle operate on the same machine rather than over a network, a user process and its corresponding server process are combined into a single process to reduce system overhead. However, when the application and Oracle operate on different machines, a user process communicates with Oracle via a separate server process. See "Variations in Oracle Configuration" [*] for more information.

Server processes (or the server portion of combined user/server processes) created on behalf of each user's application may perform one or more of the following:

To maximize performance and accommodate many users, a multi-process Oracle system uses some additional Oracle processes called background processes.

Additional Information: On many operating systems, background processes are created automatically when an instance is started. On other operating systems, the server processes are created as a part of the Oracle installation. See your Oracle operating system-specific documentation for details on how these processes are created.

An Oracle instance may have many background processes; not all are always present. The background processes in an Oracle instance include the following:

Figure 9 - 4 illustrates each background process's interaction with the different parts of an Oracle database, and the following sections describe each process. The Parallel Server is not illustrated; see Oracle7 Parallel Server Concepts & Administration.

Figure 9 - 4. The Background Processes of a Multiple-Process Oracle Instance

Database Writer (DBWR) Database Writer process (DBWR) writes buffers to datafiles. DBWR is an Oracle background process responsible for buffer cache management. For more information about the database buffer cache, see "The Database Buffer Cache" [*].

When a buffer in the buffer cache is modified, it is marked "dirty". The primary job of the DBWR process is to keep the buffer cache "clean" by writing dirty buffers to disk. As buffers are filled and dirtied by user processes, the number of free buffers diminishes. If the number of free buffers drops too low, user processes that must read blocks from disk into the cache are not able to find free buffers. DBWR manages the buffer cache so that user processes can always find free buffers.

An LRU (least recently used) algorithm keeps the most recently used data blocks in memory and thus minimizes I/O. The database writer process (DBWR) keeps blocks that are used often, for example, blocks that are part of frequently accessed small tables or indexes, in the cache so that they do not need to be read in again from disk. To make room in the buffer cache for other blocks, DBWR removes blocks that are accessed infrequently (for example, blocks that are part of very large tables or leaf blocks from very large indexes) from the system global area (SGA). For information about leaf blocks, see "The Internal Structure of Indexes" [*].

The LRU scheme causes more frequently accessed blocks to stay in the buffer cache so that when a buffer is written to disk, it is unlikely to contain data that may be useful soon. However, if the DBWR process becomes too active, it may write blocks to disk that are about to be needed again.

The buffer cache has multiple LRU latches. Latches are automatic internal locks that protect shared data structures. The initialization parameter DB_BLOCK_LRU_LATCHES controls how many latches are configured and by default is set to the number of CPUs on your system. This is usually a good value to reduce latch contention for the DBWR processes, thus improving performance.

For More Information

See "Locking Mechanisms" in Chapter 10 of Oracle7 Server Tuning.

The DBWR process writes dirty buffers to disk under the following conditions:

In the first two cases, DBWR writes the blocks on the dirty list to disk with a single multiblock write. The number of blocks written in a multiblock write varies by operating system.

A time-out occurs if DBWR is inactive for three seconds. In this case, DBWR searches a specified number of buffers on the LRU list and writes any dirty buffers that it finds to disk. Whenever a time-out occurs, DBWR searches a new set of buffers. If the database is idle, DBWR eventually writes the entire buffer cache to disk.

When a checkpoint occurs, the Log Writer process (LGWR) specifies a list of modified buffers that must be written to disk. DBWR writes the specified buffers to disk. For more information about checkpoints, see "Checkpoints" [*].

Additional Information: On some platforms, an instance can have multiple DBWRs. In such a case, if one DBWR blocks during a write to one disk, the others can continue writing to other disks. The parameter DB_WRITERS controls the number of DBWR processes. See your Oracle operating system-specific documentation for information about DBWR on your platform.

For more information about DBWR and how to monitor and tune the performance of DBWR, see the Oracle7 Server Administrator's Guide and Oracle7 Server Tuning.

Log Writer (LGWR) The Log Writer process (LGWR) writes the redo log buffer to a redo log file on disk. LGWR is an Oracle background process responsible for redo log buffer management. LGWR writes all redo entries that have been copied into the buffer since the last time it wrote. LGWR writes one contiguous portion of the buffer to disk. LGWR writes

Note: LGWR writes synchronously to the active mirrored group of online redo log files. If one of the files in the group is damaged or unavailable, LGWR can continue to write to other files in the group (an error is also logged in the LGWR trace file and in the system ALERT file). If all files in a group are damaged, or the group is unavailable because it has not been archived, LGWR cannot continue to function. See "The Online Redo Log" [*] for more information about mirrored online redo logs and how LGWR functions in this configuration.

The redo log buffer (see "The Redo Log Buffer" [*]) is a circular buffer; when LGWR writes redo entries from the redo log buffer to a redo log file, server processes can then copy new entries over the entries in the redo log buffer that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.

Note: Sometimes, if more buffer space is needed, LGWR writes redo log entries before a transaction is committed. These entries become permanent only if the transaction is later committed.

Oracle uses a "fast commit" mechanism; when a user issues a COMMIT statement, LGWR puts a commit record immediately in the redo log buffer, but the corresponding data buffer changes are deferred until it is more efficient to write them to the datafiles. The atomic write of the redo entry containing the commit record for a transaction is the single event that determines the transaction has committed (then Oracle returns a success code to the committing transaction).

When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction's redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Parallel Server configurations and distributed databases. See Oracle7 Parallel Server Concepts & Administration and the Oracle7 Server Administrator's Guide for more information about SCNs and how they are used.

In times of high activity, LGWR may write to the online redo log file using group commits. For example, assume that a user commits a transaction -- LGWR must write the transaction's redo entries to disk. As this happens, other users issue a COMMIT statement. However, LGWR cannot write to the online redo log file to commit these transactions until it has completed its previous write operation. After the first transaction's entries are written to the online redo log file, the entire list of redo entries of waiting transactions (not yet committed) can be written to disk in one operation, requiring less I/O than would transaction entries handled individually. Therefore, Oracle minimizes disk I/O and maximizes performance of LGWR. If requests to commit continue at a high rate, then every write (by LGWR) from the redo log buffer may contain multiple commit records, averaging less than one write per COMMIT.

If the CKPT background process is not present, LGWR is also responsible for recording checkpoints as they occur in every datafile's header. See "Checkpoint (CKPT)" below for more information about this background process.

Checkpoint (CKPT) When a checkpoint occurs, Oracle must update the headers of all datafiles to indicate the checkpoint. In normal situations, this job is performed by LGWR. However, if checkpoints significantly degrade system performance (usually, when there are many datafiles), you can enable the Checkpoint process (CKPT) to separate the work of performing a checkpoint from other work performed by LGWR, the Log Writer process (LGWR).

For most applications, the CKPT process is not necessary. If your database has many datafiles and the performance of the LGWR process is reduced significantly during checkpoints, you may want to enable the CKPT process.

The CKPT process does not write blocks to disk; DBWR always performs that work. The statistic DBWR checkpoints displayed by the System_Statistics monitor in Server Manager indicates the number of checkpoint messages completed, regardless of whether the CKPT process is enabled or not. See the Oracle7 Server Administrator's Guide for information about the effects of changing the checkpoint interval.

The initialization parameter CHECKPOINT_PROCESS enables and disables the CKPT process; its default is FALSE.

Note: See Oracle7 Parallel Server Concepts & Administration for additional information about CKPT in an Oracle Parallel Sever.

System Monitor (SMON) The System Monitor process (SMON) performs instance recovery at instance start up. SMON is also responsible for cleaning up temporary segments that are no longer in use; it also coalesces contiguous free extents to make larger blocks of free space available. In a Parallel Server environment, SMON performs instance recovery for a failed CPU or instance; see Oracle7 Parallel Server Concepts & Administration for more information about SMON in an Oracle Parallel Server.

SMON "wakes up" regularly to check whether it is needed. Other processes can call SMON if they detect a need for SMON to wake up.

Process Monitor (PMON) The Process Monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.

PMON also periodically checks the status of dispatcher and server processes, and restarts any that have died (but not any that Oracle has killed intentionally).

Like SMON, PMON "wakes up" regularly to check whether it is needed, and can be called if another process detects the need for it.

Recoverer (RECO) The Recoverer process (RECO) is a process used with the distributed option that automatically resolves failures involving distributed transactions. The RECO background process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process re-establishes a connection between involved database servers, it automatically resolves all in-doubt transactions.

The RECO process automatically removes rows corresponding to any resolved in-doubt transactions from each database's pending transaction table.

If the RECO background process attempts to establish communication with a remote server, and the remote server is not available or the network connection has not been re-established, RECO automatically tries to connect again after a timed interval. However, RECO waits an increasing amount of time (growing exponentially) before it attempts another connection.

For more information about distributed transaction recovery, see Oracle7 Server Distributed Systems, Volume I.

The RECO background process of an instance is only present if the system permits distributed transactions and if the DISTRIBUTED_TRANSACTIONS parameter is greater than zero. If this parameter is zero, RECO is not created during instance startup.

Archiver (ARCH) The Archiver process (ARCH) copies online redo log files to a designated storage device once they become full. ARCH is present only when the redo log is used in ARCHIVELOG mode and automatic archiving is enabled. For information on archiving the online redo log, see Chapter 22, "Recovery Structures".

Additional Information: Details of using ARCH are operating system specific; for more information, see Oracle operating system-specific documentation.

Lock (LCKn) With the Parallel Server option, up to ten Lock processes (LCK0, . . ., LCK9) provide inter-instance locking. However, a single LCK process (LCK0) is sufficient for most Parallel Server systems. See Oracle7 Parallel Server Concepts & Administration for more information about this background process.

Snapshot Refresh (SNPn) With the distributed option, up to ten Snapshot Refresh processes (SNP0, ..., SNP9) can automatically refresh table snapshots. These processes wake up periodically and refresh any snapshots that are scheduled to be automatically refreshed. If more than one Snapshot Refresh process is used, the processes share the task of refreshing snapshots.

Dispatcher Processes (Dnnn) The Dispatcher processes allow user processes to share a limited number of server processes. Without a dispatcher, each user process requires one dedicated server process. However, with the multi-threaded server, fewer shared server processes are required for the same number of users. Therefore, in a system with many users, the multi-threaded server can support a greater number of users, particularly in client-server environments where the client application and server operate on different machines.

You can create multiple dispatcher processes for a single database instance; at least one dispatcher must be created for each network protocol used with Oracle. The database administrator should start an optimal number of dispatcher processes depending on the operating system limitation on the number of connections per process, and can add and remove dispatcher processes while the instance runs.

Note: The multi-threaded server requires SQL*Net Version 2 or later. Each user process that connects to a dispatcher must do so through SQL*Net, even if both processes are running on the same machine.

In a multi-threaded server configuration, a network listener process waits for connection requests from client applications, and routes each to a dispatcher process. If it cannot connect a client application to a dispatcher, the listener process starts a dedicated server process, and connects the client application to the dedicated server. This listener process is not part of an Oracle instance; rather, it is part of the networking processes that work with Oracle. See your SQL*Net documentation for more information about the network listener.

When an instance starts, the listener opens and establishes a communication pathway through which users connect to Oracle. Then, each dispatcher gives the listener an address at which the dispatcher listens for connection requests. When a user process makes a connection request, the listener process examines the request and determines if the user can use a dispatcher. If so, the listener process returns the address of the dispatcher process with the lightest load and the user process directly connects to the dispatcher.

Some user processes cannot communicate with the dispatcher (such as users connected using pre-Version 2 SQL*Net) and the network listener process cannot connect such users to a dispatcher. In this case, the listener creates a dedicated server and establishes an appropriate connection.

Trace Files, the ALERT File, and Background Processes

Each server and background process can write to an associated trace file. When a process detects an internal error, it dumps information about the error to its trace file. If an internal error occurs and information is written to a trace file, the administrator should contact Oracle support. See Oracle7 Server Messages.

All filenames of trace files associated with a background process contain the name of the process that generated the trace file. The one exception to this is trace files generated by Snapshot Refresh processes.

Trace file information can also provide information for tuning applications or an instance. Background processes always write to a trace file when appropriate. However, trace files are written on behalf of server processes (in addition to being written to when there is an internal error) only if the initialization parameter SQL_TRACE is set to TRUE. Regardless of the current value for this parameter, each session can enable or disable trace logging on behalf of the associated server process by using the SQL command ALTER SESSION with the SQL_TRACE parameter. For example, the following statement enables writing to a trace file for the session:


Each database also has an ALERT file. The ALERT file of a database is a chronological log of messages and errors, including

Oracle uses the ALERT file to keep a log of these special operations as an alternative to displaying such information on an operator's console (although many systems display information on the console). If an operation is successful, a message is written in the ALERT file as "completed" along with a timestamp.

Oracle Memory Structures

Oracle uses memory to store the following information:

The basic memory structures associated with Oracle include:

The following topics are included in this section:

Virtual Memory

On many operating systems, Oracle takes advantage of virtual memory. Virtual memory is an operating system feature that offers more apparent memory than is provided by real memory alone and more flexibility in using main memory.

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.

Software Code Areas

Software code areas are portions of memory used to store code that is being or may be executed. The code for Oracle is stored in a software area, which is typically at a location different from users' programs -- a more exclusive or protected location.

Size of Software Areas

Software areas are usually static in size, only changing when software is updated or reinstalled. The size required varies by operating system.

Read-Only, Shared and Non-Shared

Software areas are read-only and may be installed shared or non-shared. 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 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.

System Global Area (SGA)

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, the data in the instance's SGA is "shared" among the users. Consequently, the SGA is often referred to as either the "System Global Area" or the "Shared Global Area".

As described in "An Oracle Instance" [*], 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 is a shared memory area; all users connected to a multiple-process database instance may use information contained within the instance's SGA. The SGA is also writable; several processes write to the SGA during execution of Oracle.

The SGA contains the following subdivisions:

The Database Buffer Cache

The database buffer cache is a portion of the SGA that holds copies of the data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

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.

The LRU Algorithm and Full Table Scans In one particular case, the user process puts the newly read block's buffer on the LRU end of the list. When the process is performing a full table scan, the blocks for the table are read and put in buffers on the LRU end of the list. This is because a fully scanned table will most likely be needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

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.

The Redo Log Buffer

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 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.

The Shared Pool

The shared pool is an area in the SGA that contains three major areas: library cache, dictionary cache, and control structures. The following figure shows the contents of the shared pool.

Figure 9 - 5. Contents of the Shared Pool

The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. Increasing the value of this parameter increases the amount of memory reserved for the shared pool, and therefore the space reserved for shared SQL areas.

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:

persistent area

The persistent area contains bind information that persists across executions, code for datatype conversion (in case the defined datatype is not the same as the datatype of the selected column), and other state information (like recursive or remote cursor numbers or the state of a parallel query).The size of the persistent area depends on the number of binds and columns specified in the statement. For example, the persistent area is larger if many columns are specified in a query.

runtime area

The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the type and complexity of the SQL statement being executed and on the sizes of the rows that are processed by the statement. In general, the runtime area is somewhat smaller for INSERT, UPDATE, and DELETE statements than it is for SELECT statements.

The runtime area is created as the first step of an execute request. For INSERT, UPDATE, and DELETE statements, the runtime area is freed after the statement has been executed. For queries, the runtime area is freed only after all rows are fetched or the query is canceled.

A private SQL area continues to exist until the corresponding cursor is closed. Since Oracle frees the runtime area after the statement completes, generally only the persistent area remains waiting. Application developers should close all open cursors that will not be used again to minimize the amount of memory required for users of the application.

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:

This information is useful as reference material for database administrators, application designers, and end users alike. Oracle itself accesses the data dictionary frequently during the parsing of SQL statements. This access is essential to the continuing operation of Oracle. See Chapter 8, "The Data Dictionary," for more information on the data dictionary.

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.

Allocation and Reuse of Memory in the Shared Pool In general, any item (shared SQL area or dictionary row) in the shared pool remains present until it is flushed according to a modified LRU algorithm. The memory for items not being regularly used is freed if space is required for new items that must be allocated some space in the shared pool. By using a modified LRU algorithm, shared pool items that are shared by many sessions can remain in memory as long as they are useful, even if the process that originally created the item is terminated. As a result, the overhead and processing of SQL statements associated with a multi-user Oracle system is kept to a minimum.

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:

Note: A shared SQL area can be flushed from the shared pool, even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to execute its statement, Oracle reparses the statement and a new shared SQL area is allocated in the shared pool.

Some unique circumstances can also cause a shared SQL area to be flushed from the shared pool. When the ANALYZE command is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements that reference the analyzed 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 object. Shared SQL areas are also dependent on the objects referenced in the corresponding SQL statement. If a referenced object is modified, the shared SQL area is invalidated (marked invalid) and must be reparsed the next time the statement is executed. See Chapter 16, "Dependencies Among Schema Objects", for more information about the invalidation of SQL statements and dependency issues.

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.

Other SGA Information

Information also stored in the SGA includes the following:

The amount of memory dedicated to all shared areas in the SGA can have performance impact; see the Oracle7 Server Administrator's Guide for more information.

Size of the SGA

The size of the SGA is determined at instance start up. For optimal performance in most systems, the entire SGA should fit in real memory. If the entire SGA does not fit in real memory and virtual memory is used to store parts of the SGA, 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 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:


The size, in bytes, of a single data block and database buffer.


The number of database buffers, each the size of DB_BLOCK_SIZE, allocated for the SGA. (The total amount of space allocated for the database buffer cache in the SGA is DB_BLOCK_SIZE times DB_BLOCK_BUFFERS.)


The number of bytes allocated for the redo log buffer.


The size in bytes of the area devoted to shared SQL and PL/SQL statements.

The memory allocated for an instance's SGA is displayed on instance startup when using Server Manager. You can also see the current instance's SGA size using the Server Manager command SHOW and the SGA option. See the Server Manager's User's Guide for more information about the Server Manager command SHOW, and the Oracle7 Server Administrator's Guide for discussions of the above initialization parameters and how they affect the SGA. See your installation or user's guide for information specific to your operating system.

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.

Program Global Area (PGA)

The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background). Consequently, the PGA is referred to as the "Program Global Area" or the "Process Global Area."

Contents of a PGA

A PGA is allocated by Oracle when a user process connects to an Oracle database and a session is created, though this varies by operating system and configuration. See "Connections, Sessions, and User Processes" [*] 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.

Non-Shared and Writable

The PGA is non-shared memory area to which a process can write. One PGA is allocated for each server process; the PGA is exclusive to a server process and is read and written only by Oracle code acting on behalf of that process.

Size of a PGA

A PGA's size is operating system specific, and not dynamic. When the client and server are on different machines, the PGA is allocated on the database server at connect time; if sufficient memory is not available to connect, an error occurs. This error is an Oracle error in an operating system error number range. Once connected, a user can never run out of PGA space; there is either enough or not enough memory to connect in the first place.

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.

Sort Areas

Sorting requires space in memory. Portions of memory in which Oracle sorts data are called sort areas. A sort area exists in the memory of an Oracle user process that requests a sort. A sort area can grow to accommodate the amount of data to be sorted but is limited by the value of the initialization parameter SORT_AREA_SIZE. The value of this parameter is expressed in bytes. The default value varies depending on your operating system.

During a sort, Oracle may perform some tasks that do not involve referencing data in the sort area. In such cases, Oracle may decrease the size of the sort area by writing some of the data to a temporary segment on disk and then deallocating the portion of the sort area that contained that data. Such deallocation may occur, for example, if Oracle returns control to the application. The size to which the sort area is reduced is determined by the initialization parameter SORT_AREA_RETAINED_SIZE. The value of this parameter is expressed in bytes. The minimum value is the equivalent of one database block, the maximum and default value is the value of the SORT_AREA_SIZE initialization parameter. Memory released during a sort is freed for use by the same Oracle process, but it is not released to the operating system.

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.

Sort Direct Writes

Sort Direct Writes provides an automatic tuning method for deriving the size and number of direct write buffers based upon the sort area size. The memory for the buffers is taken from the sort area, so only one tuning parameter is necessary. In addition, an optimizer cost model is provided.

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.

For More Information

See Oracle7 Server Administrator's Guide.

Variations in Oracle Configuration

All connected Oracle users must execute two modules of code to access an Oracle database instance:

application or Oracle tool

A database user executes a database application (such as a precompiler program) or an Oracle tool (such as an Oracle Forms application), which issues SQL statements to an Oracle database.

Oracle server code

Each user has some Oracle server code executing on his/her behalf, which interprets and processes the application's SQL statements.

In a multiple-process instance, the code for connected users can be configured in one of three variations:

Additional Information: Some operating systems offer a choice of configurations; see your Oracle operating system-specific documentation for more details on your options. The following sections describe each variation in more detail.

Connections, Sessions, and User Processes

Before describing the variations in Oracle configurations, it is useful to introduce the terms "connection" and "session", which are related to the term "user process" (see "User Processes" [*]), but are very different in meaning.

A connection is a communication pathway between a user process and an Oracle instance. A communication pathway is established using available inter-process communication mechanisms (on a computer that executes both the user process and Oracle) or network software (when different computers execute the database application and Oracle, and communicate via a network).

A session is a specific connection of a user to an Oracle instance via a user process; for example, when a user starts SQL*Plus, the user must provide a valid username and password and then a session is established for the user. A session lasts from the time the user connects until the time the user disconnects (or exits the database application).

Multiple sessions can be created and concurrently exist for a single Oracle user; for example, a user with the username/password of SCOTT/TIGER can connect to the same Oracle instance several times using the same username.

When the multi-threaded server is not used, a server process is created on behalf of each user session; however, when the multi-threaded server is used, a single server process can be shared among many user sessions; each of the following sections describes the relationship of sessions and processes with respect to the configuration variations.

Oracle Using Combined User/Server Processes

Figure 9 - 7 illustrates one configuration of Oracle. Notice that in this configuration, the database application and the Oracle server code all run in the same process, termed a user process.

Figure 9 - 7. Oracle Using Combined User/Server Processes

This configuration of Oracle (sometimes called single-task Oracle) is only feasible in operating systems that can maintain a separation between the database application and the Oracle code in a single process (such as on the VAX VMS operating system). This separation is required for data integrity and privacy. Some operating systems, such as UNIX, cannot provide this separation and thus must have separate processes run application code from server code to prevent damage to Oracle by the application.

Note: The program interface is responsible for the separation and protection of the Oracle server code and is responsible for passing data between the database application and the Oracle user program. See "The Program Interface" [*] for more information about this structure.

Only one Oracle connection is allowed at any time by a process using the above configuration. However, in a user-written program it is possible to maintain this type of connection while concurrently connecting to Oracle using a network (SQL*Net) interface.

Oracle Using Dedicated Server Processes

Figure 9 - 8 illustrates Oracle running on two computers using the dedicated server architecture.

Notice that in this type of system, a user process executes the database application on one machine and a server process executes the associated Oracle server on another machine. These two processes are separate, distinct processes. The separate server process created on behalf of each user process is called a dedicated server process (or shadow process) because this server process acts only on behalf of the associated user process.

In this configuration (sometimes called two-task Oracle), every user process connected to Oracle has a corresponding dedicated server process. Therefore, there is a one-to-one ratio between the number of user processes and server processes in this configuration. Even when the user is not actively making a database request, the dedicated server process remains (though it is inactive and may be paged out on some operating systems).

The dedicated server architecture of Oracle allows client applications being executed on client workstations to communicate with another computer running Oracle across a network. This is illustrated in Figure 9 - 8. However, this configuration of Oracle is also used if the same computer executes both the client application and the Oracle server code, but the host operating system cannot maintain the separation of the two programs if they were to be run in a single process. A common example of such an operating system is UNIX.

Figure 9 - 8. Oracle Using Dedicated Server Processes

The program interface allows the communication between the two programs. In the dedicated server configuration, communications between the user and server processes is accomplished using different mechanisms:

Additional Information: These communications links are operating system and installation dependent; see your Oracle operating system-specific documentation and the SQL*Net documentation for more information.

See "The Program Interface" [*] for additional information about the program interface.

The Multi-Threaded Server

The multi-threaded server configuration allows many user processes to share very few server processes. Without the multi-threaded server configuration, each user process requires its own dedicated server process; a new server process is created for each client requesting a connection. A dedicated server process remains associated to the user process for the remainder of the connection. With the multi-threaded server configuration, many user processes connect to a dispatcher process. The dispatcher routes client requests to the next available shared server process. The advantage of the multi-threaded server configuration is that system overhead is reduced, so the number of users that can be supported is increased.

Contrasting Dedicated Server Processes and Shared Server Processes

Consider an order entry system with dedicated server processes. A customer places an order as a clerk enters the order into the database. For most of the transaction, the clerk is on the telephone talking to the customer and the server process dedicated to the clerk's user process remains idle. The server process is not needed during most of the transaction, and the system is slower for other clerks entering orders.

The multi-threaded server configuration eliminates the need for a dedicated server process for each connection. A small number of shared server processes can perform the same amount of processing as many dedicated server processes. Also, the amount of memory required for each user is relatively small. Because less memory and process management are required, more users can be supported.

An Overview of the Multi-Threaded Server Architecture

The following types of processes are needed in a system using the multi-threaded server architecture:

The network listener process waits for incoming connection requests and determines if each user process can use a shared server process. If so, the listener gives the user process the address of a dispatcher process. If the user process requests a dedicated server, the listener process creates a dedicated server process and connects the user process to it. (At least one dispatcher process must be configured and started per network protocol that the database clients will use.)

Note: To use shared servers, a user process must connect through SQL*Net, even if the user process is on the same machine as the Oracle instance.

A request from a user is a single program interface call that is part of the user's SQL statement. When a user makes a call, its dispatcher places the request on the request queue in the SGA, where it is picked up by the next available shared server process. The shared server processes make all the necessary calls to the database to complete each user process's request. When the server completes the request, the server returns the results to the response queue of the dispatcher that the user is connected to the SGA. The dispatcher then returns the completed request to the user process.

In the order entry system example, each clerk's user process connects to a dispatcher; each request made by a clerk is sent to a dispatcher, which places the request in the request queue. The next available shared server process picks up the request, services it, and puts the response in the response queue. When a clerk's request is completed, the clerk remains connected to the dispatcher, but the shared server process that processed the request is released and available for other requests. While one clerk is talking to a customer, not making a request to the database, another clerk can use the same shared server process.

Figure 9 - 9 illustrates how user processes communicate with the dispatcher across the two-task interface and how the dispatcher communicates users' requests to shared server processes.

Figure 9 - 9. The Oracle Multi-Threaded Server Configuration and Shared Server Processes

Shared Server Processes

Shared server processes and dedicated server processes provide the same functionality, except shared server processes are not associated with a specific user process. Instead, a shared server process serves any client request in the multi-threaded server configuration.

The PGA of a shared server process does not contain user-related data; such information needs to be accessible to all shared server processes. The PGA of a shared server process contains only stack space and process-specific variables. "Program Global Area (PGA)" [*] provides more information about the content of a PGA in different types of instance configurations.

All session-related information is contained in the SGA. Each shared server process needs to be able to access all sessions' data spaces so that any server can handle requests from any session. Space is allocated in the SGA for each session's data space. You can limit the amount of space that a session can allocate by setting the resource limit PRIVATE_SGA to the desired amount of space in the user's profile. See Chapter 17, "Database Access," for more information about resource limits and profiles.

Oracle dynamically adjusts the number of shared server processes based on the length of the request queue. The number of shared server processes that can be created ranges between the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.

Dispatcher Request and Response Queues

The request queue is in the SGA and is common to all dispatcher processes of an instance. The shared server processes check the common request queue for new requests, picking up new requests on a first-in-first-out basis. One shared server process picks up one request in the queue and makes all necessary calls to the database to complete that request. The shared server process then places the response on the calling dispatcher's response queue. Each dispatcher has its own response queue in the SGA and each dispatcher is responsible for sending completed requests back to the appropriate user process.

Artificial Deadlocks

With a limited number of shared server processes, the possibility of an "artificial" deadlock can arise. An artificial deadlock can occur in the following situation:

When Oracle detects an artificial deadlock, new shared server processes are automatically created as needed until the original user submits a request that releases the locked resources causing the artificial deadlocks. If the maximum number of shared server processes (as specified by the MTS_MAX_SERVERS parameter) have been started, the database administrator must manually resolve the deadlock by disconnecting a user. This releases a shared server process, resolving the artificial deadlock.

If artificial deadlocks occur too frequently on your system, you should increase the value of MTS_MAX_SERVERS.

Restricted Operations of the Multi-Threaded Server

Certain administrative activities cannot be performed while connected to a dispatcher process, including shutting down or starting an instance and media recovery. An error message is issued if you attempt to perform these activities while connected to a dispatcher process.

These activities are typically performed when connected as INTERNAL. When you want to connect as INTERNAL in system configured with multi-threaded servers, you must state in your connect string that you want to use a dedicated server process instead of a dispatcher process (SRVR=DEDICATED).

Additional Information: See your Oracle operating system-specific documentation or SQL*Net documentation for the proper connect string syntax.

Examples of How Oracle Works

Now that the memory structures, processes, and varying configurations of an Oracle database system have been discussed, it is helpful to see how all the parts work together. The following sections demonstrate and contrast the two-task and multi-threaded server Oracle configurations.

An Example of Oracle Using Dedicated Server Processes

The following example is a simple illustration of the dedicated server architecture of Oracle:

These steps show only the most basic level of operations that Oracle performs.

An Example of Oracle Using the Multi-Threaded Server

The following example is a simple illustration of the multi-threaded server architecture of Oracle:

Note: If the user is using SQL*Net Version 1 or 1.1, the SQL*Net listener process creates a dedicated server process on behalf of the user process and the remainder of the example operates as described in the preceding example. (Users using earlier versions of SQL*Net cannot use a shared server process.)

The Program Interface

The program interface is the software layer between a database application and Oracle. The program interface does the following:

The Oracle code acts as a server, performing database tasks on behalf of an application (a client), such as fetching rows from data blocks. It consists of several parts, provided by both Oracle software and operating system-specific software.

Program Interface Structure

The program interface consists of the following pieces:

Both the user and Oracle sides of the program interface execute Oracle software, as do the drivers.

SQL*Net is the portion of the program interface that allows the client application program and the Oracle Server to reside on separate computers in your communication network.

The Program Interface Drivers

Drivers are pieces of software that transport data, usually across a network. They perform operations like connect, disconnect, signal errors, and test for errors. Drivers are specific to a communications protocol. There is always a default driver.

You may install multiple drivers (such as the asynchronous or DECnet drivers), and select one as the default driver, but allow an individual user to use other drivers by specifying the desired driver at the time of connection. Different processes can use different drivers. A single process can have concurrent connections to a single database or to multiple databases (either local or remote) using different SQL*Net drivers.

The installation and configuration guide and SQL*Net documentation for your system contains details about choosing and installing drivers and adding new drivers after installation. The SQL*Net documentation describes selecting a driver at runtime while accessing Oracle.

Operating System Communications Software

The lowest level software connecting the user side to the Oracle side of the program interface is the communications software, which is provided by the host operating system. DECnet, TCP/IP, LU6.2, and ASYNC are examples.

Additional Information: The communication software may be supplied by Oracle Corporation but is usually purchased separately from the hardware vendor or a third party software supplier. See your Oracle operating system-specific documentation for more information about the communication software of your system.

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index