Page 64
A final parameter that is of use is the Boolean LOG_CHECKPOINTS_TO_ALERT. This places a stamp in the alert.log file for the database whenever a checkpoint occurs, and is valuable when trying to pinpoint the exact checkpoint interval.
Dispatcher Processes (Dnnn) As mentioned, server processes can be either dedicated to a user process, or be shared among user processes. Using shared servers requires configuring the Multi-Threaded Server (MTS), as discussed in Chapter 18, "Installing and Configuring the Oracle Web Application Server." When using shared server processes, at least one dispatcher process must be present, and more can be present, depending on the needs of the environment. The dispatcher process passes user requests to the SGA request queue, and returns the server responses back to the correct user process.
The number of dispatcher processes is controlled using a number of init.ora parameters. The MTS_DISPATCHERS parameter specifies the protocol the dispatcher uses, as well as the number of dispatchers to start that use the protocol. Multiple protocol groups can be configured using multiple MTS_DISPATCHERS lines. A typical MTS_DISPATCHERS line might look like:
MTS_DISPATCHERS = "tcp, 4" MTS_DISPATCHERS = "spx, 2"
Multiple protocol groups can also be configured within the same MTS_DISPATCHERS parameter, like so:
MTS_DISPATCHERS = ("tcp, 4", "spx, 2")
The MTS_MAX_DISPATCHERS parameter controls the maximum number of dispatcher processes allowed for the RDBMS. (See Chapter 18 for more details on configuring the MTS services.)
ARCH The Archiver process is responsible for copying full online redo logs to the archived redo log files. This only occurs when the database is operating in archivelog mode. While the archiver is copying the redo log, no other processes can write to the log. This is important to keep in mind, because of the circular nature of the redo logs. If the database needs to switch redo logs, but the archiver is still copying the next log in the sequence, all database activity halts until archiver finishes. Also note that if ARCH is for some reason unable to finish copying the log, it waits until the error stopping it from finishing the write is resolved.
It is important to note that the ARCHIVE_LOG_START parameter in the init.ora file must be set to TRUE for ARCH to automatically start upon database open. Placing the database in archivelog mode is in itself not enough to cause ARCH to automatically start. If you do configure archivelog mode, and don't automatically start the ARCH process, the database hangs when all online redo logs fill, waiting for you to manually archive the online logs.
CKPT CKPT, the checkpoint process, is an optional background process that performs the checkpoint tasks that LGWR would normally perform, namely updating the data file and control file headers with the current version information. Enable this process to reduce the amount of work on LGWR when there are frequent checkpoints occurring, frequent log switches, or many data files in the database.
Setting the CHECKPOINT_PROCESS parameter to TRUE enables the CKPT process. All other parameters related to checkpoints that are previously described also hold true when the CKPT process is running.
Page 65
RECO RECO, the recovery process, is responsible for recovering failed transactions in distributed database systems. It is automatically started when the database is configured for distributed transactions (that is, when the DISTRIBUTED_TRANSACTIONS init.ora parameter is set to a value greater than zero). The RECO process operates with little or no DBA intervention when an in-doubt transaction occurs in a distributed system. The RECO process attempts to connect to the remote database, and resolves the in-doubt transaction when a database connection is successful. (See Chapter 28, "Distributed Database Management," for more information on RECO and the two phase commit.)
SNPn SNPn, the snapshot process, handles the automatic refreshing of database snapshots and runs the database procedures scheduled through the DBMS_JOB package. The init.ora parameter JOB_QUEUE_PROCESS sets how many snapshot processes are started, and JOB_QUEUE_INTERVAL determines how long (in seconds) the snapshot processes sleep before waking to process any pending jobs or transactions.
LCKn In a parallel server environment, multiple instances mount one database. The Lock process is responsible for managing and coordinating the locks held by the individual instances. Each instance in the parallel server installation has one to 10 lock processes assigned, and each instance must have the same number. This process has no purpose in a non-parallel server environment. See Chapter 27, "Parallel Server Management," for more information on the Lock background process.
Pnnn Parallel query processes are named Pnnn. The Oracle server starts and stops query processes, depending on database activity and your configuration of the parallel query option. These processes are involved in parallel index creations, table creations, and queries. There are always as many processes started as specified in the PARALLEL_MIN_SERVERS parameter, and there is never more than is specified by PARALLEL_MAX_SERVERS.
For more information on configuring the parallel query processes, see Chapter 27.
User and Server Processes (Snnn) Applications and utilities access the RDBMS through a user process. The user process connects to a server process, which can be dedicated to one user process or shared between many. The server process parses and executes SQL statements that are submitted to it and returns the result sets back to the user process. It is also the process that reads data blocks from the data files into the database buffer cache.
Each user process is allocated a section of memory referred to as the Process Global Area (PGA). The contents of the PGA differ depending on what type of connection is made to the database. When a user process connects to the database via a dedicated server process, user session data, stack space, and cursor state information is stored in the PGA. The user session data consists of security and resource usage information; the stack space contains local variables specific to the user session; and the cursor state area contains runtime information for the cursor, including rows returned, and cursor return codes. If, however, the user process connects through a shared server process, the session and cursor state information is stored within the SGA. Although this does not increase the memory requirements for the database as a whole, it does require a larger SGA to hold the extra session information.