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 connections.

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 shared_pool.

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:

L 7-6

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:

L 7-7

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:

L 7-8

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

Connection 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 parameter:

L 7-9

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.

L 7-10

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.

L 7-11

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.

L 7-12

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.