Page 66
To gain a better understanding of how all the above components of the instance interact, let's look at a typical transaction as it moves through the instance structures.
A transaction begins when a user session connects to a server session using SQL*Net driver. This connection can be a dedicated connection with its own server process, or a shared connection handled through a Dispatcher process. The server session hashes the SQL statement passed it and compares that hash number with the hash numbers of statements already saved in the shared SQL area. If an exact duplicate of the statement is found in the Shared Pool, the parsed form of the statement and the execution plan that are already stored are used. If a match is not found in the Shared Pool, the Server session parses the statement.
Next, the server session checks to see whether the data blocks necessary to complete the transaction are already stored in the database buffer cache. If the blocks are not in the cache, the server session reads the necessary blocks from the data files and copies them into the cache. If the transaction is a query, the server session returns the results of the query to the user session (performing the data block read and copy as many times as necessary to return all data).
For a transaction that modifies data there are more steps involved. For this example, assume the transaction is an update. After the necessary data blocks are read into the buffer cache, the blocks in memory are modified. Modifying cached blocks marks them as dirty, and they are placed on the dirty list. Redo information is also generated, and is stored in the redo log cache.
The transaction continues until one of several things happens. If the transaction is relatively short lived (for example, an update to one row of sales data), it finishes and the user commits, which signals LGWR to flush the redo log buffer to the online redo log files. If the transaction is fairly long and complex, any of the following may happen:
While the transaction is processing, with redo being generated to the redo cache and flushed, the online redo logs gradually fill. When the current log fills, LGWR begins writing to the next log group, while ARCH copies the redo log to disk or tape. Because the transaction never records as successful until all redo log information is written from the redo log buffer to the
Page 67
online redo logs, both LGWR and ARCH must be able to complete their respective tasks without error.
For the majority of the time, the SGA and Oracle background processes operate without administrator intervention. However, there are times when problems must be diagnosed and fixed. There are several methods available to the DBA to monitor and track the behavior of the instance and its associated structures.
The best place to find information about instance problems is in the trace files of the processes themselves. These trace files are written to the location defined in the USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the specific process and the error encountered. When a background process is terminated or abnormally aborts an operation, it usually produces a trace file containing the error message(s) causing the failure, dumps of the current process stacks, currently executing cursors, and any other information pertinent to the problem. Although some of this information is useful to you as a DBA, it is more important to collect and forward these trace files to Oracle World Wide Customer Support consultants who may be helping you diagnose your problems. They have tools available to pinpoint exactly where the problem occurs. Background process failures also usually write an entry into the alert.log file for the database, or to their own separate trace files located in the directory specified by the init.ora parameter BACKGROUND_DUMP_DEST.
Background processes can also be tracked through the OS, using system commands. In a UNIX environment, each background process is a separate task, and can therefore be tracked separately. It is often very valuable to look at OS memory and CPU utilization of processes (using such tools as sar, ps, vmstat, and top) to identify performance problems or run-away queries. Sometimes the only way to resolve a hung or broken server or user processes is by terminating them at the OS level. Use caution, however, when attempting to modify or terminate any other Oracle background process; most background processes will crash the entire database if abnormally terminated.
In a Windows NT server environment, tracking the background processes is a little
trickier. This is because the entire Oracle instance is implemented on the Windows NT OS as a
single background process called a service. The individual background processes are
implemented as threads belonging to the service. Although there are plenty of utilities available on
Windows NT to track and monitor the behavior of processes, thread administration tools
are fairly uncommon. One solution is to use the Performance Monitor utility that ships with
the Windows NT OS to monitor, among other things, the memory consumption and
context switches of all the threads belonging to the service. By converting the SPID column from the