Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be examining the process structure in Oracle, which is a mechanism used by the operating system.
Processes, a mechanism used by the operating system, are used to perform a number of serial tasks.
Client Processes
When a user connects to the database with an application such as TOAD or SQL*Plus, the operating system starts a “client process” for that user.
Client Processes are different from Oracle database processes that communicate directly with the database.
While Oracle database processes can read/write to the SGA area, client processes cannot.
While Oracle database processes only run on the server on which the database runs, client processes can also run on a server other than the server on which the database runs.
Connection: It is the physical communication path between a client process and the database instance.
Session: It is a logical concept that shows the status of the user after logging into the database.
A user’s session information is learned as follows.
1 2 3 4 5 6 | SQL> SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE USERNAME='BRC'; SID SERIAL# PADDR ---------- ---------- ---------------- 132 455 00000000C2529280 197 221 00000000C252A338 |
A session is killed as follows.
1 2 3 | SQL> ALTER SYSTEM KILL SESSION '197, 221'; System altered. |
Server Processes
Client processes from users cannot communicate directly with the database.
Oracle database creates a server process for each client process so that user processes can communicate with the database.
Dedicated Server Process
It is faster than Shared Server Process.
It is like having a separate waiter for each customer sitting at each table in the restaurant.
Shared Server Process
In this process structure, client applications communicate with the database via a “dispatcher process” instead of a server process over the network.
There is no separate server process for each client process.
For example, 10 client processes can be connected through a single dispatcher process.
So, instead of one waiter for every table in the restaurant, there is 1 waiter for every 5 tables.
Dispatcher queues requests in the “Request Queue” field located in the “Large Pool” in the SGA area.
The first idle shared server process receives the request from the “Request Queue” field, processes it and writes the result to the “Response Queue” field.
Dispatcher forwards the resulting transaction to the user from the “Response Queue” area.
