In
order to communicate with oracle database, oracle users need a program
such as SQL *Plus which can issue SQL statements and few processes which
can execute these SQL statements. These processes are divided into User Process, Server Process and Background Processes.
User process runs user application like SQL *Plus. Server process
manages oracle user process’s requests. Server process executes SQL
statements and returns result to user process. Background processes are
the core of oracle database which handle over all database operations.
Dedicated server connection:
A dedicated process has one to one relationship with user process. The
user connected with dedicated server connection has the dedicated
resource that is whether or not the user is doing work the connection
remains allocated to that user. Thus if your user is not working and if
there is less network resource then this user is blocking another user
to connect or to execute the user request by making this user works
queued. This connection is preferable when you have lot of
resource or to some administrative user like "SYS" , "SYS" for
administrative works and for batch jobs,
Shared server connection:
Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue.
Network Listener process listens the user process request. If user
process request requires a dedicated server process, listener process
starts a dedicated server process. If
the request can be assigned to a shared server process, then the
request is forwarded to dispatcher process. Shared server configuration
requires at least on dispatcher process. Dispatcher process places the
request on request queue. Request Queue is created in SGA and shared by
all dispatcher processes. On of free Shared server process picks up the
request from request queue. After processing the request, shared server
process places the result in response queue. Each dispatcher process has
its own response queue in SGA. Dispatcher knows about the user process
which placed the request, so the response is returned back to user
process.
When
there is shared server the user connection is shared, that is user is
connecting by dispatchers and when a user is idle his resource can be
used by other users, thus lessen the load on system, this is more likely
in a environment where user is mostly idle, like in a order entry
system.
So, what should I choose?
If
your environment is OLTP then shared is preferable because the tasks
need short time. Whether is Warehouse, OLAP, Data Mining the users are
running time consuming and heavy load query where it is best to use
dedicated server.
How to configure a shared server configuration?
Below are the two links that helps in configuring the same.
