Oracle7 Server Concepts
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:
1. A database server machine is currently running Oracle using multiple background processes.
2. A client workstation runs a database application (in a user process) such as SQL*Plus. The client application attempts to establish a connection to the server using a SQL*Net driver.
3. The database server is currently running the proper SQL*Net driver. The Listener process on the database server detects the connection request from the client database application and creates a dedicated server process on the database server on behalf of the user process.
4. The user executes a single SQL statement. For example, the user inserts a row into a table.
5. The dedicated server process receives the statement. At this point, two paths can be followed to continue processing the SQL statement:
- If the shared pool contains a shared SQL area for an identical SQL statement, the server process can use the existing shared SQL area to execute the client's SQL statement.
- If the shared pool does not contain a shared SQL area for an identical SQL statement, a new shared SQL area is allocated for the statement in the shared pool.
In either case, a private SQL area is created in the session's PGA and the dedicated server process checks the user's access privileges to the requested data.
6. The server process retrieves data blocks from the actual datafile, if necessary, or uses data blocks already stored in the buffer cache in the SGA of the instance.
7. The server process executes the SQL statement stored in the shared SQL area. Data is first changed in the SGA. It is permanently written to disk when the DBWR process determines it is most efficient to do so. The LGWR process records the transaction in the online redo log file only on a subsequent commit request from the user.
8. If the request is successful, the server sends a message across the network to the user. If it is not successful, an appropriate error message is transmitted.
9. Throughout this entire procedure, the other background processes are running and watching for any conditions that require intervention. In addition, Oracle is managing other transactions and preventing contention between different transactions that request the same data.
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:
1. A database server is currently running Oracle using the multi-threaded server configuration.
2. A client workstation runs a database application (in a user process) such as SQL*Forms. The client application attempts to establish a connection to the database server using the proper SQL*Net driver.
3. The database server machine is currently running the proper SQL*Net driver. The Listener process on the database server detects the connection request of the user process and determines how the user process should be connected. If the user is using SQL*Net Version 2, the Listener informs the user process to reconnect using the address of an available dispatcher process.
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.)
4. The user issues a single SQL statement. For example, the user updates a row into a table.
5. The dispatcher process places the user process's request on the request queue, which is in the SGA and shared by all dispatcher processes.
6. An available shared server process checks the common dispatcher request queue and picks up the next SQL statement on the queue. It then processes the SQL statement as described in Steps 5, 6, and 7 of the previous example. Note in Step 5 that parts of the session's private SQL area are created in the SGA.
7. Once the shared server process finishes processing the SQL statement, the process places the result on the response queue of the dispatcher process that sent the request.
8. The dispatcher process checks its response queue and sends completed requests back to the user process that made the request.