ch02.htm

Teach yourself Oracle8 in 21 day

	they are not. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077"><B>The Oracle Instance</B></FONT></H2>
<P>The Oracle instance consists of the Oracle processes and shared memory necessary
to access information in the database. The instance is made up of the user processes,
the Oracle background processes, and the shared memory used by these processes (see
Figure 2.3).</P>
<H3><FONT COLOR="#000077"><B>The Oracle Memory Structure</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Oracle uses shared memory
for several purposes, including caching of data and indexes as well as storing shared
program code. This shared memory is broken into various pieces, or <I>memory structures</I>.
The basic memory structures associated with Oracle are the System Global Area (SGA)
and the Program Global Area (PGA).</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 2.3.</B></A></P>
<P><I>The Oracle instance.</I></P>
<H3><FONT COLOR="#000077"><B>The System Global Area (SGA)</B></FONT></H3>
<P>The SGA is a shared memory region that Oracle uses to store data and control information
for one Oracle instance. The SGA is allocated when the Oracle instance starts and
deallocated when the Oracle instance shuts down. Each Oracle instance that starts
has its own SGA. The information in the SGA consists of the following elements, each
of which has a fixed size and is created at instance startup:</P>
<P>The database buffer cache--This stores the most recently used data blocks. These
blocks can contain modified data that has not yet been written to disk (sometimes
known as <I>dirty blocks</I>), blocks that have not been modified, or blocks that
have been written to disk since modification (sometimes known as <I>clean blocks</I>).
Because the buffer cache keeps blocks based on a most recently used algorithm, the
most active buffers stay in memory to reduce I/O and improve performance.

<UL>
	<LI>The redo log buffer--This stores redo entries, or a log of changes made to the
	database. The redo log buffers are written to the redo log as quickly and efficiently
	as possible. Remember that the redo log is used for instance recovery in the event
	of a system failure.
	<P>
	<LI>The shared pool--This is the area of the SGA that stores shared memory structures
	such as shared SQL areas in the library cache and internal information in the data
	dictionary. The shared pool is important because an insufficient amount of memory
	allocated to the shared pool can cause performance degradation. The shared pool consists
	of the library cache and the data-dictionary cache.
</UL>

<P><FONT COLOR="#000077"><B>The Library Cache</B></FONT></P>
<P>The library cache is used to store shared SQL. Here the parse tree and the execution
plan for every unique SQL statement are cached. If multiple applications issue the
same SQL statement, the shared SQL area can be accessed by each to reduce the amount
of memory needed and to reduce the processing time used for parsing and execution
planning.</P>
<P><FONT COLOR="#000077"><B>The Data-Dictionary Cache</B></FONT></P>
<P>The data dictionary contains a set of tables and views that Oracle uses as a reference
to the database. Oracle stores information here about the logical and physical structure
of the database. The data dictionary contains information such as the following:

<UL>
	<LI>User information, such as user privileges
	<P>
	<LI>Integrity constraints defined for tables in the database
	<P>
	<LI>Names and data types of all columns in database tables
	<P>
	<LI>Information on space allocated and used for schema objects
</UL>

<P>The data dictionary is frequently accessed by Oracle for the parsing of SQL statements.
This access is essential to the operation of Oracle; performance bottlenecks in the
data dictionary affect all Oracle users. Because of this, you should make sure that
the data-dictionary cache is large enough to cache this data. If you do not have
enough memory for the data-dictionary cache, you see a severe performance degredation.
If you ensure that you have allocated sufficient memory to the shared pool where
the data-dictionary cache resides, you should see no performance problems.</P>
<H3><FONT COLOR="#000077"><B>The Program Global Area (PGA)</B></FONT></H3>
<P>The PGA is a memory area that contains data and control information for the Oracle
server processes. The size and content of the PGA depends on the Oracle server options
you have installed. This area consists of the following components:

<UL>
	<LI>Stack space--This is the memory that holds the session's variables, arrays, and
	so on.
	<P>
	<LI>Session information--If you are not running the multithreaded server, the session
	information is stored in the PGA. If you are running the multithreaded server, the
	session information is stored in the SGA.
	<P>
	<LI>Private SQL area--This is an area in the PGA where information such as binding
	variables and runtime buffers is kept.
</UL>

<H2><FONT COLOR="#000077"><B>Processes</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>In many operating systems,
traditional processes have been replaced by <I>threads</I> or <I>lightweight processes</I>.
The term <I>process</I> is used in this book to describe a thread of execution, or
a mechanism that can execute a set of code; <I>process</I> refers to the mechanism
of execution and can refer to a traditional process or a thread.</P>
<P>The Oracle RDBMS uses two types of processes: user processes and Oracle processes
(also known as background processes). In some operating systems (such as Windows
NT), these processes are actually threads; for the sake of consistency, I will refer
to them as <I>processes</I>.</P>
<H3><FONT COLOR="#000077"><B>User Processes</B></FONT></H3>
<P>User, or client, processes are the user's connections to the RDBMS system. The
user process manipulates the user's input and communicates with the Oracle server
process through the Oracle program interface. The user process is also used to display
the information requested by the user and, if necessary, can process this information
into a more useful form.</P>
<H3><FONT COLOR="#000077"><B>Oracle Processes</B></FONT></H3>
<P>Oracle processes perform functions for users. Oracle processes can be split into
two groups: server processes (which perform functions for the invoking process) and
background processes (which perform functions on behalf of the entire RDBMS).</P>
<P><FONT COLOR="#000077"><B>Server Processes (Shadow Processes)</B></FONT></P>
<P>Server processes, also known as shadow processes, communicate with the user and
interact with Oracle to carry out the user's requests. For example, if the user process
requests a piece of data not already in the SGA, the shadow process is responsible
for reading the data blocks from the datafiles into the SGA. There can be a one-to-one
correlation between user processes and shadow processes (as in a dedicated server
configuration); although one shadow process can connect to multiple user processes
(as in a multithreaded server configuration), doing so reduces the utilization of
system resources.</P>
<P><FONT COLOR="#000077"><B>Background Processes</B></FONT></P>
<P>Background processes are used to perform various tasks within the RDBMS system.
These tasks vary from communicating with other Oracle instances and performing system
maintenance and cleanup to writing dirty blocks to disk. Following are brief descriptions
of the nine Oracle background processes:

<UL>
	<LI>DBWR (Database Writer)--DBWR is responsible for writing dirty data blocks from
	the database block buffers to disk. When a transaction changes data in a data block,
	that data block need not be immediately written to disk. Therefore, the DBWR can
	write this data to disk in a manner that is more efficient than writing when each
	transaction completes. The DBWR usually writes only when the database block buffers
	are needed for data to be read. Data is written in a least recently used fashion.
	For systems in which asynchronous I/O (AIO) is available, there should be only one
	DBWR process. For systems in which AIO is not available, performance can be greatly
	enhanced by adding more DBWR processes.
	<P>
	<LI>LGWR (Log Writer)--The LGWR process is responsible for writing data from the
	log buffer to the redo log.
	<P>
	<LI>CKPT (Checkpoint)--The CKPT process is responsible for signaling the DBWR process
	to perform a checkpoint and to update all the datafiles and control files for the
	database to indicate the most recent checkpoint. A checkpoint is an event in which
	all modified database buffers are written to the datafiles by the DBWR. The CKPT
	process is optional. If the CKPT process is not present, the LGWR assumes these responsibilities.
	<P>
	<LI>PMON (Process Monitor)--PMON is responsible for keeping track of database processes
	and cleaning up if a process prematurely dies (PMON cleans up the cache and frees
	resources that might still be allocated). PMON is also responsible for restarting
	any dispatcher processes that might have failed.
	<P>
	<LI>SMON (System Monitor)--SMON performs instance recovery at instance startup. This
	includes cleaning temporary segments and recovering transactions that have died because
	of a system crash. The SMON also defragments the database by coalescing free extents
	within the database.
	<P>
	<LI>RECO (Recovery)--RECO is used to clean transactions that were pending in a distributed
	database. RECO is responsible for committing or rolling back the local portion of
	the disputed transactions.
	<P>
	<LI>ARCH (Archiver)--ARCH is responsible for copying the online redo log files to
	archival storage when they become full. ARCH is active only when the RDBMS is operated
	in <TT>ARCHIVELOG</TT> mode. When a system is not operated in <TT>ARCHIVELOG</TT>
	mode, it might not be possible to recover after a system failure. It is possible
	to run in <TT>NOARCHIVELOG</TT> mode under certain circumstances, but typically should
	operate in <TT>ARCHIVELOG</TT> mode.
	<P>
	<LI>LCK<I>n</I> (Parallel Server Lock)--Up to 10 LCK processes are used for interinstance
	locking when the Oracle Parallel Server option is used.
	<P>
	<LI>D<I>nnn</I> (Dispatcher)--When the Multithreaded Server option is used, at least
	one Dispatcher process is used for every communications protocol in use. The Dispatcher
	process is responsible for routing requests from the user processes to available
	shared server processes and back.
</UL>

<H2><FONT COLOR="#000077"><B>How Transactions Work</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>To give you a better idea
how Oracle operates, this section analyzes a sample transaction. Throughout this
book, the term <I>transaction</I> is used to describe a logical group of work that
can consist of one or many SQL statements and must end with a commit or a rollback.
Because this example is of a client/server application, SQL*Net is necessary. The
following steps are executed to complete the transaction:

<DL>
	<DD><B>1.</B> The application processes the user input and creates a connection to
	the server via SQL*Net.
	<P><B>2. </B>The server picks up the connection request and creates a server process
	on behalf of the user.</P>
	<P><B>3.</B> The user executes a SQL statement or statements. In this example, the
	user changes the value of a row in a table.</P>
	<P><B>4.</B> The server process checks the shared pool to see whether there is a
	shared SQL area that has this identical SQL statement. If it finds an identical shared
	SQL area, the server process checks whether the user has access privileges to the
	data. If so, the server process uses the shared SQL area to process the request.
	If a shared SQL area is not found, a new shared SQL area is allocated, and the statement
	is parsed and executed.</P>
	<P><B>5.</B> The server process finds the data in the SGA (if it is present there)
	or reads the data from the datafile into the SGA.</P>
	<P><B>6. </B>The server process modifies the data in the SGA. Remember that the server
	processes can read only from the datafiles. At some later time, the DBWR process
	writes the modified blocks to permanent storage.</P>
	<P><B>7.</B> The user executes either the <TT>COMMIT</TT> or <TT>ROLLBACK</TT> statement.
	A <TT>COMMIT</TT> will finalize the transaction, a <TT>ROLLBACK</TT> will undo the
	changes. If the transaction is being committed, the LGWR process immediately records
	the transaction in the redo log file.</P>
	<P><B>8.</B> If the transaction is successful, a completion code is returned across
	the network to the client process. If a failure has occurred, an error message is
	returned.
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>A transaction is not considered
	committed until the write to the redo log file is complete. This arrangement ensures
	that in the event of a system failure, a committed transaction can be recovered.
	If a transaction has been committed, it is set in stone. 
<HR>


</BLOCKQUOTE>

<P>While transactions occur, the Oracle background processes do their jobs, keeping
the system running smoothly. While this process occurs, hundreds of other users might
be performing similar tasks. Oracle's job is to keep the system in a consistent state,
to manage contention and locking, and to perform at the necessary rate.</P>
<P>This overview is intended to give you an understanding of the complexity and amount