Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

3.6 Accessing a Database

The previous sections described
the process of starting up and shutting down a database. But the
database is only part of a complete systemyou also need a
client process to access the database, even if that process is on the
same physical machine as the database.

3.6.1 Server Processes and Clients

To access a database, a user
connects to the instance that provides access to the desired
database. A program that accesses a database is really composed of
two distinct piecesa client program and a server
processthat connect to the Oracle instance. For example,
running the Oracle character-mode utility SQL*Plus involves two
processes:

The SQL*Plus process itself, acting as the client

The Oracle server process, sometimes referred to as a
shadow process,
that provides the connection to the Oracle instance

3.6.1.1 Server process

The Oracle server process always runs on the
computer on which the instance is running. The server process
attaches to the shared memory used for the SGA and can read from it
and write to it.

As the name implies, the server process works for the client
processit reads and passes back the requested data, accepts
and makes changes on behalf of the client, and so on. For example,
when a client wants to read a row of data stored in a particular
database block, the server process identifies the desired block and
either retrieves it from the database buffer cache or reads it from
the correct datafile and loads it into the database buffer cache.
Then, if the user requests changes, the server process modifies the
block in the cache and generates and stores the necessary redo
information in the redo log buffer in the SGA. The server process,
however, does not write the redo information from the log buffer to
the redo log files, and it does not write the modified database block
from the buffer cache to the datafile. These actions are performed by
the Log Writer (LGWR) and Database Writer (DBWR) processes,
respectively.

3.6.1.2 Client process

The client
process can run on the same machine as the instance or on a separate
computer. A network connects the two computers and provides a way for
the two processes to talk to each other. In either case, the concept
is essentially the sametwo processes are involved in the
interaction between a client and the database. When both processes
are on the same machine, Oracle uses local communications via
Inter Process Communication
(IPC); when the client is on one machine and the database server is
on another, Oracle uses Oracle Net over the network to communicate
between the two machines.

3.6.2 Application Servers and Web Servers as Clients

Although the discussion in the
previous section used the terms client and
server extensively, please
don't assume that Oracle is strictly a client/server
database. Oracle was one of the early pioneers of client/server
computing and has long been based on the notion of two tasks: a
client and a server. But, when you consider multi-tier computing
involving web and application servers, the notion of a client changes
somewhat. The "client" process
becomes the middle tier, or application server. You can logically
consider any process that connects to an Oracle instance a client in
the sense that it is served by the database. Don't
confuse this usage of the term
"client" with the actual client in
a multi-tier configuration. The eventual client in a multi-tier model
is some type of program providing a user interfacefor example,
a browser running Java.

The Oracle Application Server, which is part
of the overall Oracle platform, is designed to act as this middle
tier. The Oracle Application Server works seamlessly with the Oracle
database and shares some of the same technology.
Oracle's Application Server is described in more
detail in Chapter 14.

Figure 3-5 illustrates users connecting to an Oracle instance to
access a database in both two-tier and three-tier configurations,
involving local and network communication. The figure is based on a
simplified version of the instance figure used earlier in Chapter 2
(Figure 2-8).

Figure 3-5 highlights the server process connection models as opposed
to the interaction of the background processes. There is a
traditional two-tier client/server connection on the left side, a
three-tier connection with an application server on the right side,
and a local client connection in the middle of the figure. The
two-tier and three-tier connections use a network to communicate with
the database, while the local client uses local IPC.

Figure 3-5. Accessing a database

3.6.3 Oracle Net and Establishing Network Connections

The server processes shown in
Figure 3-5 are connected to the client processes using some kind of
network. How do client processes get hooked up with Oracle server
processes to begin working?

The matchmaker that arranges marriages between Oracle clients and
server processes is called the Oracle Net Listener. The Listener
"listens" for incoming connection
requests for one or more instances. The Listener is not part of the
Oracle instanceit directs connection requests to the instance.
The Listener is started and stopped independently of the instance. If
the Listener is down and the instance is up, clients accessing the
database over a network cannot find the instance because there is no
Listener to guide them. If the Listener is up and the instance is
down, there is nowhere to send clients.

The Listener's function is relatively simple:

The client contacts the Listener over the network.

The Listener detects an incoming request and introduces the
requesting client to an Oracle server process.

The Listener introduces the server to the client by letting each know
the other's network address.

The Listener steps out of the way and lets the client and server
communicate directly.

Once the client and the server know how to find each other, they
communicate directly. The Listener is no longer required.

Figure 3-6 illustrates the previous steps for establishing a
networked connection. Network traffic appears as dotted lines.

Figure 3-6. Connecting with the Oracle Net Listener

3.6.4 The Shared Server/Multi-Threaded Server

The server processes shown in the above diagrams are
dedicated;
they serve only one client process. So, if an application
has 1,000 clients, the Oracle instance will have 1,000 corresponding
server processes. Each server process uses system resources such as
the memory and the CPU. Scaling to large user populations can consume
a lot of system resources. To support the ever-increasing demand for
scalability, Oracle introduced the Multi-Threaded Server
(MTS) in Oracle7.

Since Oracle9i, the
Multi-Threaded Server is called the shared
server.

Shared servers allow the Oracle instance to share a set of server
processes across a larger group of users. Instead of each
client's connecting to and using a dedicated server,
the clients use shared servers, which can significantly reduce the
overall resource requirements for serving large numbers of users.

In many systems there are times when the clients
aren't actively using their server process, such as
when users are reading and absorbing data retrieved from the
database. When a client is not using its server process in the
dedicated model, that
server process still has a hold on system resources even though it
isn't doing any useful work. In the
shared server model,
the shared server can use the resources of an inactive client to do
work for another client process.

You don't have to make a mutually exclusive choice
between shared server processes and dedicated server processes for an
Oracle instance. Oracle can mix and match dedicated and shared
servers, and clients can connect to one or the other. The choice is
based on your Oracle Net configuration files. In the configuration
files there will be one service name that leads the client to a
dedicated server, and another for connecting via shared servers. The
Oracle Net manuals provide the specific syntax for this
configuration.

The type of server process a client is using is transparent to the
client. From a client perspective, the multithreading or sharing of
server processes happens "under the
covers," on the database server. The same Listener
handles dedicated and multithreaded connection requests.

The steps the Listener takes in establishing a shared server
connection are a little different and involve some additional
background processes for the instance dispatchers and the shared
servers themselves:

Dispatchers

In the previous
description of the Listener, you saw how it forms the connection
between a client and server process and then steps out of the way.
The client must now be able to depend on a server process that is
always available to complete the connection. Because a shared server
process may be servicing another client, the client connects to a
dispatcher, which is always ready to receive any client request.
There are separate dispatchers for each network protocol being
used (e.g., dispatchers for TCP/IP, etc.). The dispatchers serve as
surrogate dedicated servers for the clients. Clients directly connect
to their dispatchers instead of to a server. The dispatchers accept
requests from clients and place them in a request queue, which is a memory
structure in the SGA. There is one request queue for each instance.

Shared servers

The shared
server processes read from the request queue, process the requests,
and place the results in the response queue for the appropriate
dispatcher. There is one response queue for each dispatcher. The
dispatcher then reads the results from the response queue and sends
the information back to the client process.

There is a pool of dispatchers and a pool of shared servers. Oracle
starts a certain number of each based on the
initialization parameters

DISPATCHERS and SERVERS (MTS_DISPATCHERS and
MTS_SERVERS prior to
Oracle9i). You can start additional dispatchers
up to the maximum number indicated by the value of the
MAX_DISPATCHERS (MTS_MAX_DISPATCHERS
prior to Oracle9i) parameter. Oracle will start
additional shared servers up to the value of

MAX_SHARED_SERVERS (MTS_MAX_SERVERS prior to
Oracle9i). If Oracle starts additional processes
to handle a heavier request load and the load dies down again, Oracle
gradually reduces the number of processes to the floor specified by
SERVERS.

The following steps show how
establishing a connection and using shared server processes differ
from using a dedicated server process:

The client contacts the Listener over the network.

The Listener detects an incoming request and, based on the Oracle Net
configuration, determines that it is for a multithreaded server.
Instead of handing the client off to a dedicated server, the Listener
hands the client off to a dispatcher for the network protocol the
client is using.

The Listener introduces the client and the dispatcher by letting each
know the other's network address.

Once the client and the dispatcher know where to find each other,
they communicate directly. The Listener is no longer required. The
client sends each work request directly to the dispatcher.

The dispatcher places the client's request in the
request queue in the SGA.

The next available shared server process reads the request from the
request queue and does the work.

The shared server places the results for the
client's request in the response queue for the
dispatcher that originally submitted the request.

The dispatcher reads the results from its queue.

The dispatcher sends the results to the client.

Figure 3-7 illustrates the steps for using the shared servers.
Network traffic appears as dotted lines.

Figure 3-7. Connecting with the Oracle Net Listener (Shared Server)

3.6.5 Session Memory for Shared Server Processes Versus Dedicated Server Processes

There is a concept in Oracle known as session memory or
state.
State information is basically data that describes the current status
of a session in Oracle. For example, state information contains
information about the SQL statements executed by the session. When
you use a dedicated server, this state is stored in the private
memory used by the dedicated server. This works out well because the
dedicated server works with only one client. The technical term for
this private memory is the Program Global Area (PGA).

If you're using the shared servers, however, any server can
work on behalf of a specific client. The session state cannot be
stored in the PGA of the shared server process. All servers must be
able to access the session state because the session can migrate
between different shared servers. For this reason, Oracle places this
state information in the SGA.

All servers can read from the SGA. Putting the state information in the
SGA allows a session and its state to move from one shared server to
another for processing different requests. The server that picks up
the request from the request queue simply reads the session state
from the SGA, updates the state as needed for processing, and puts it
back in the SGA when processing has finished.

The request and response queues, as well as the session state,
require additional memory in the SGA, so you should allocate more
memory if you're using shared servers. By default,
the memory for the shared server session state comes from the shared
pool. Alternatively, in Oracle8, you could also configure something
called the large pool as a
separate area of memory for the MTS. (We introduced the large pool in
Chapter 2 in Section 2.3.1.) Using the large pool memory
avoids the overhead of coordinating memory usage with the shared SQL,
dictionary caching, and other functions of the shared pool. This
allows memory management from the large pool and avoids competing
with other subsystems for space in and access to the shared pool.
Note that Oracle Database 10g introduces
automated memory management of the SGA and PGA size.

3.6.6 Data Dictionary Information About the Shared Server

The data dictionary, which we introduced in
Chapter 2, also contains information about the operation of the
shared server in the following views:

V$MTS/V$SHARED_SERVER_MONITOR

This view
contains dynamic information about the shared servers, such as high-water
marks for connections and how many shared servers have been started
and stopped in response to load variations. The view V$MTS was
deprecated in Oracle9i and replaced by
V$SHARED_SERVER_MONITOR.

V$DISPATCHER

This
view contains details of the dispatcher processes used by the shared
server. It can determine how busy the dispatchers are, to help set
the floor and ceiling values appropriately.

V$SHARED_SERVER

This view contains details of the shared server processes used by the
shared server. It can determine how busy the servers are, to help set
the floor and ceiling values appropriately.

V$CIRCUIT

You can think of the route from a client to its dispatcher and from
the dispatcher to the shared server (using the queues) as a virtual
circuit. This view details these virtual circuits for user
connections.