Re: SGA Processes

I quote from teh Oracle Concepts manual

The following example illustrates an Oracle configuration where the user and associated server process are on separate machines (connected via a network).

An instance is currently running on the computer that is executing Oracle (often called the host or database server).

A computer running an application (a local machine or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Net8 driver.

The server is running the proper Net8 driver. The server detects the connection request from the application and creates a (dedicated) server process on behalf of the user process.

The user executes a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.

The server process receives the statement and checks the shared pool for any shared SQL area that contains an identical SQL statement. If a shared SQL area is found, the server process checks the user's access privileges to the requested data and the previously existing shared SQL area is used to process the statement; if not, a new shared SQL area is allocated for the statement so that it can be parsed and processed.

The server process retrieves any necessary data values from the actual datafile (table) or those stored in the system global area.

The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction committed, the LGWR process immediately records the transaction in the online redo log file.

If the transaction is successful, the server process sends a message across the network to the application. If it is not successful, an appropriate error message is transmitted.

Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.

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

See Also:
Chapter 8, "Process Architecture" for more information about Oracle configuration.

Note that the "server process" mentioned several times is the process dedicated to the session.
(in MTS, it may service several session).

bp325 wrote:

> Dear Guru's,
>
> I'd like some advice regarding server SGA processes to get a good basic
> understanding as to what happens from when a user session connects to the
> database.
> I'm familiar with the following but can not visualise what process actually
> writes data to the rollback segments, or flushes the data away from the
> rollback segment on a commit;
>
> Please correct me where you think my logic may be flawed and/or advise me in
> the gaps below.
>
> Thanks,
> Bhavesh
> ~~~~~~
>
> A user connects to the database via SQL Net Driver.
> They request some data for update via a SQL statement and if the SQL is
> present within the shared pool then an existing SQL area is used, else a new
> shared SQL area is allocated.
> The server process retrieves data from cached blocks within the database
> buffer cache. (a cache hit) or retrieves the data from the datafile (any
> idea of what process actually does this?). I assume at this point a
> read-consistent view is also stored within the rollback segment (what server
> process does this?).
>
> The DBWR process writes data to disk from the dirty buffers in the Database
> buffer cache and the LGWR process records the process from the redo log
> buffer pool to the online redo log on commit else the transaction is rolled
> back. (I can't figure out which process will roll this back)
> DBWR writes to disk when no free buffers are found or a checkpoint occurs.
> LGWR writes to online redo on commit, checkpoint or when the redo log buffer
> pool is a third full.
> ARCH process copies redo log files to disk/tape on a log switch.
>
> Cheers again.
Received on Mon Feb 26 2001 - 16:44:05 CST