Oracle Shared Servers Tips
Oracle Tips by Burleson Consulting
When your database server experiences a large
volume of incoming connections,
the overhead of spawning a dedicated process to service each request
can cause measurable overhead on the server. This is because the
default listener process “bequeaths” the incoming connection, creating
a process (PID) on the Oracle server and directing this process to
establish the connection to Oracle.
To reduce this overhead, the MTS can be
implemented to allow new connections to attach to prespawned shadow
processes. Note that Oracle does not recommend using the MTS unless
you average more than 300 connections on the server.
The basic premise of the MTS is that Oracle
creates dispatcher processes, each with a set of pre-established
connections into the Oracle database. Each dispatcher owns a set of
pre-spawned connections into the database. By pre-establishing the
connections to Oracle, server resources are minimized, and RAM storage
is also minimized because each session will not allocate a personal
sort_area_size in the Program Global Area (PGA). Instead, all
dispatchers share the same User Global Area (UGA), thereby reducing
memory demands on the database server.
One of the problems with the dedicated listener
is that each incoming transaction is spawned by the listener as a
separate operating system task. With the MTS, all communications to a
database are handled through a single dispatcher instead of separate
UNIX process IDs (PIDs) on each database. If you have constant
connection loads of 300 users or more, using the MTS translates into
faster performance for most online tasks. The only real downside to
using the MTS is that the Remote DBA cannot directly observe Oracle
connections using the UNIX ps-ef|grep oracle command.
However, be aware that the MTS is not a
panacea, especially at times when
you want to invoke a dedicated process for your program. For Pro*C
programs and I/O-intensive SQL*Forms applications, or any batch
processes that have little idle time, you may derive better
performance using a dedicated listener process. For shops that
segregate tasks into online and batch modes, the Remote DBA sometimes creates
separate listeners—one with the MTS and another for dedicated
In general, the MTS offers benefits such as
reduced memory use, fewer processes per user, and automatic load
balancing. However, the Remote DBA must be careful to set the proper number
of dispatcher processes, and the proper number of servers within each
dispatcher. Also, because the MTS uses the shared_pool for
process sorting, the Remote DBA will also see increased demands on the
Connections using the MTS will place the UGA
inside the Oracle SGA. To hold the UGA storage for MTS connections,
Oracle has provided the large_pool init.ora parameter. The
LARGE pool is an area of the SGA similar to the SHARED pool, but with
restrictions on its usage such that only certain types and sizes of
memory can be allocated in this pool. When using the MTS, Oracle
recommends that the large_pool be set to a value greater than
the default of 614,000 bytes.
Inside Oracle, the v$queue and v$dispatcher
system views will indicate if the number of MTS dispatchers is too
low. Even though the number of dispatchers is specified in the
init.ora file, you can change it online in SQL*Remote DBA with the
alter system command:
ALTER SYSTEM SET MTS_DISPATCHERS = 'TCPIP,4';
If you encounter problems with the MTS, you can quickly
regress to dedicated servers by issuing an alter system
command. The following command turns off the MTS by setting the number
of MTS servers to zero:
ALTER SYSTEM SET MTS_SERVERS=0;
The Remote DBA must be careful when bouncing the database and
listener. In some cases, the instance must be bounced if the listener
is stopped, or it will restart in dedicated mode. Whenever an instance
is to be bounced, stop the listener, shut down the instance, restart
the listener, and start up the instance. The listener reads the MTS
parameters only if it is running before startup of the instance.
Therefore, bouncing the listener can disable the MTS. To implement the
MTS, you need to add the following init.ora parameters:
# Multi-threaded Server parameters
Now that we see how the MTS can relieve stress on the
server, let's look at the connection pooling features of Oracle8i.
Connection Pooling and Network Performance
pooling is a resource utilization feature that enables you to reduce
the number of physical network connections to an MTS dispatcher. This
reduction is achieved by sharing or pooling a set of connections among
the client processes. Connection pooling effectively allows Oracle to
maximize the number of physical network connections to the
Multi-Threaded Server. Connection pooling is achieved by sharing or
pooling a dispatcher's set of connections among multiple client
processes (Figure 7-1).
Figure 7-36: Multi-Threaded Server connections
Connection pooling reuses physical connections
and makes them available for incoming clients, while still maintaining
a logical session with the previous idle connection. By using a
timeout mechanism to temporarily release transport connections that
have been idle for a specified period, connection pooling will
suspend a previous connection and reuse the physical connection.
When the idle client has more work to do, the physical connection is
reestablished with the dispatcher. When the idle client has more work
to do, the physical connection is reestablished with the dispatcher.
By default, connection pooling is disabled on
both incoming and outgoing network connections. To enable connection
pooling, you must alter the mts_dispatchers parameter in the
init.ora file. Next, we can enable the Oracle NET connection
pooling feature by adding the POOL argument to the mts_dispatchers
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=3)"
If a number is specified, then connection
pooling is enabled for both incoming and outgoing network connections
and the number specified is the timeout in ticks for both incoming and
outgoing network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=ON)"
If ON, YES, TRUE, or BOTH is specified,
connection pooling is enabled for both incoming and outgoing network
connections and the default timeout (set by Oracle NET) will be used
for both incoming and outgoing network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=IN)"
If IN is specified, connection pooling is
enabled for incoming network connections and the default timeout (set
by Oracle NET) will be used for incoming network connections.
MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)(POOL=OUT)"
If OUT is specified, connection pooling is
enabled for outgoing network connections and the default timeout (set
by Oracle NET) will be used for outgoing network connections.
In practice, connection pooling is rarely used
except in cases where the database server is overwhelmed with incoming
Oracle NET requests.
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.