Oracle9i Database Performance Guide and Reference
Release 1 (9.0.1)

Part Number A87503-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Configuring Shared Servers

This chapter contains the following topics:

Configuring the Number of Shared Servers

Performance of certain database features can improve when a shared server architecture is used, and performance of certain database features can degrade slightly when a shared server architecture is used. For example, with BFILEs, if a query has not completed, then BFILEs must close and reopen in other servers when migrated. With parallel execution, a session can be prevented from migrating to another shared server while they are active.

A session can remain nonmigratable after a request from the client has been processed. Use of the mentioned features can make sessions nonmigratable, because the features have not stored all the user state information in the UGA, but have left some of the state in the PGA. As a result, if different shared servers process requests from the client, then the part of the user state stored in the PGA is inaccessible. To avoid this, individual shared servers often need to remain bound to a user session. This makes the session nonmigratable among shared servers.

When using these features, you might need to configure more shared servers. This is because some servers might be bound to sessions for an excessive amount of time.

This section discusses how to reduce contention for processes used by Oracle's architecture:

Identifying Contention Using the Dispatcher-Specific Views

The following views provide dispatcher performance statistics:

V$DISPATCHER provides general information about dispatcher processes. V$DISPATCHER_RATE view provides dispatcher processing statistics.

See Also:


Analyzing V$DISPATCHER_RATE Statistics

The V$DISPATCHER_RATE view contains current, average, and maximum dispatcher statistics for several categories. Statistics with the prefix CUR_ are statistics for the current session. Statistics with the prefix AVG_ are the average values for the statistics since the collection period began. Statistics with the prefix MAX_ are the maximum values for these categories since statistics collection began.

To assess dispatcher performance, query the V$DISPATCHER_RATE view and compare the current values with the maximums. If your present system throughput provides adequate response time and current values from this view are near the average and below the maximum, then you likely have an optimally tuned shared server environment.

If the current and average rates are significantly below the maximums, then consider reducing the number of dispatchers. Conversely, if current and average rates are close to the maximums, then you might need to add more dispatchers. A general rule is to examine V$DISPATCHER_RATE statistics during both light and heavy system use periods. After identifying your shared server load patterns, adjust your parameters accordingly.

If needed, you can also mimic processing loads by running system stress tests and periodically polling the V$DISPATCHER_RATE statistics. Proper interpretation of these statistics varies from platform to platform. Different types of applications also can cause significant variations on the statistical values recorded in V$DISPATCHER_RATE.

Reducing Contention for Dispatcher Processes

This section discusses how to add dispatcher processes and how to enable connection pooling.

Adding Dispatcher Processes

Add dispatcher processes while Oracle is running with the SET option of the ALTER SYSTEM statement to increase the value for the DISPATCHERS initialization parameter.

The total number of dispatcher processes is limited by the value of the initialization parameter MAX_DISPATCHERS. You might need to increase this value before adding dispatcher processes. The default value of this parameter is five, and the maximum value varies depending on your operating system.

See Also:

Oracle9i Database Administrator's Guide and Oracle9i Net Services Administrator's Guide for more information on adding dispatcher processes 

Enabling Connection Pooling

When system load increases and dispatcher throughput is maximized, it is not necessarily a good idea to immediately add more dispatchers. Instead, consider configuring the dispatcher to support more users with connection pooling.

DISPATCHERS lets you enable various attributes for each dispatcher. Oracle supports a name-value syntax to let you specify attributes in a position-independent, case-insensitive manner. For example:


The optional attribute POOL enables the Oracle Net connection pooling feature. TICK is the size of a network TICK in seconds. The TICK default is 15 seconds.

See Also:

Oracle9i SQL Reference and the Oracle9i Net Services Administrator's Guide for more information about the DISPATCHERS parameter and its options 

Enabling Session Multiplexing

Multiplexing is used by a connection manager process to establish and maintain connections from multiple users to individual dispatchers. For example, several user processes can connect to one dispatcher by way of a single connection from a connection manager process.

The connection manager manages communication from users to the dispatcher by way of a shared connection. At any one time, zero, one, or a few users might need the connection, while other user processes linked to the dispatcher by way of the connection manager process are idle. This way, multiplexing is beneficial because it maximizes use of user-to-dispatcher process connections.

Multiplexing is also useful for multiplexing database link connections between dispatchers. The limit on the number of connections for each dispatcher is platform dependent. For example:


Reducing Contention for Shared Servers

This section discusses how to identify contention for shared servers and how to increase the maximum number of shared servers.

Identifying Contention for Shared Servers

Steadily increasing wait times in the requests queue indicate contention for shared servers. To examine wait time data, use the dynamic performance view V$QUEUE. This view contains statistics showing request queue activity for shared servers. By default, this view is available only to the user SYS and to other users with SELECT ANY TABLE system privilege, such as SYSTEM. The following columns show wait times for requests in the queue:


Displays the total waiting time, in hundredths of a second, for all requests that have ever been in the queue


Displays the total number of requests that have ever been in the queue

Monitor these statistics occasionally while your application is running by issuing the following SQL statement:



This query returns the results of a calculation that show the following:


From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue before processing.

You can also determine how many shared servers are currently running by issuing the following query:

SELECT COUNT(*) "Shared Server Processes"


The result of this query could look like the following:


If you detect resource contention with shared servers, then first make sure that this is not a memory contention issue by examining the shared pool and the large pool. If performance remains poor, then you might want to create more resources to reduce shared server process contention. Do this by modifying the optional server process parameters, as explained below.

Setting and Modifying Shared Server Processes

This section explains how to set optional parameters affecting processes for the shared server architecture. This section also explains how and when to modify these parameters to tune performance.

The following static initialization parameters are discussed in this section:

This section also describes the following initialization/session parameters:

Values for the initialization parameters MAX_DISPATCHERS and MAX_SHARED_SERVERS define upper limits for the number of dispatchers and servers running on an instance. These parameters are static and cannot be changed after your database is running. You can create as many dispatcher and server processes as you need, but the total number of processes cannot exceed the host operating system's limit for the number of running processes.


Setting MAX_DISPATCHERS sets the limit on the number of dispatchers for all DISPATCHERS' dispatcher values.  

You can also define starting values for the number of dispatchers and servers by setting the DISPATCHERS parameter's DISPATCHER attribute and the SHARED_SERVERS parameter. After system startup, you can dynamically reset values for these parameters to change the number of dispatchers and servers using the SET option of the ALTER SYSTEM statement. If you enter values for these parameters in excess of limits set by the static parameters, then Oracle uses the static parameter values.

The default value of MAX_SHARED_SERVERS is dependent on the value of SHARED_SERVERS. If SHARED_SERVERS is less than or equal to 10, then MAX_SHARED_SERVERS defaults to 20. If SHARED_SERVERS is greater than 10, then MAX_SHARED_SERVERS defaults to two times the value of SHARED_SERVERS.

Self-adjusting Shared Server Architecture Features

When the database starts, SHARED_SERVERS is the number of shared servers created. Oracle does not allow the number of shared servers to fall below this minimum. During processing, Oracle automatically adds shared servers up to the limit defined by MAX_SHARED_SERVERS if Oracle perceives that the load based on the activity of the requests on the common queue warrant additional shared servers. Therefore, you are unlikely to improve performance by explicitly adding shared servers. However, you might need to adjust your system to accommodate certain resource issues.

If the number of shared server processes has reached the limit set by the initialization parameter MAX_SHARED_SERVERS and the average wait time in the request queue is still unacceptable, then you might improve performance by increasing the MAX_SHARED_SERVERS value.

If resource demands exceed expectations, then you can either allow Oracle to automatically add shared server processes or you can add shared processes by altering the value for SHARED_SERVERS. You can change the value of this parameter in the initialization parameter file, or alter it using the SHARED_SERVERS parameter of the ALTER SYSTEM statement. Experiment with this limit and monitor shared servers to determine an ideal setting for this parameter.

Setting the Shared Server Highwater Mark Equal to MAX_SHARED_SERVERS

This is the first stage in troubleshooting a shared server architecture. Performance can degrade if there are not enough shared servers to process all the requests put toward the database.

Check for the initial setting of the maximum number of shared servers. For example:


Check for the highwater mark for shared servers. For example:

SELECT maximum_connections "MAXIMUM_CONNECTIONS", 

servers_started "SERVERS_STARTED", servers_terminated "SERVERS_TERMINATED", 
servers_highwater "SERVERS_HIGHWATER" 

The output is:

------------------- --------------- ------------------ ----------------- 
                 60              30                 30                50 

If the system is not performing well, then HIGHWATER should not be equal to the parameter MAX_SHARED_SERVERS regularly or for a long period of time.

See Also:

Oracle9i Database Reference for complete information on V$SHARED_SERVER_MONITOR 

Increasing the Maximum Number of Shared Servers

The shared servers are the processes that perform data access and pass back this information to the dispatchers.

The dispatchers then forward the data to the client process. If there are not enough shared servers to handle all the requests, then the queue backs up (V$QUEUE), and requests take longer to process. However, before you check the V$QUEUE statistics, it is best to first check if you are running out of shared servers.

Find out the amount of free RAM in the system. Examine ps or any other operating system utility to find out the amount of memory a shared server uses. Divide the amount of free RAM by the size of a shared server. This gives you the maximum number of shared servers you can add to your system.

The best way to proceed is to increase the MAX_SHARED_SERVERS parameter gradually until you begin to swap. If swapping occurs due to the shared server, then reduce the number until swapping stops, or increase the amount of physical RAM. Because each operating system and application is different, the only way to find out the correct setting for MAX_SHARED_SERVERS is through trial and error.

To change the MAX_SHARED_SERVERS, first edit the initialization parameter file. Save the file and restart the instance. Remember that setting SHARED_SERVERS to MAX_SHARED_SERVERS should only be done if you are sure that you will be using the machine at 100% all the time. Keep in mind the following rules:

Determining the Optimal Number of Dispatchers and Shared Servers

As mentioned, SHARED_SERVERS determines the number of shared servers activated at instance startup. The default setting for SERVER_SERVERS is one, which is the default setting when DISPATCHERS is specified.

To determine the optimal number of dispatchers and shared servers, consider the number of users typically accessing the database and how much processing each requires. Also consider that user and processing loads vary over time. For example, a customer service system's load might vary drastically from peak OLTP-oriented daytime use to DSS-oriented nighttime use. System use can also predictably change over longer time periods, such as the loads experienced by an accounting system that vary greatly from mid-month to month-end.

If each user makes relatively few requests over a given period of time, then each associated user process is idle for a large percentage of time. In this case, one shared server process can serve 10 to 20 users. If each user requires a significant amount of processing, then establish a higher ratio of servers to user processes.

In the beginning, it is best to allocate fewer shared servers. Additional shared servers start automatically as needed and are deallocated automatically if they remain idle too long. However, the initial servers always remain allocated, even if they are idle.

If you set the initial number of servers too high, then your system might incur unnecessary overhead. Experiment with the number of initial shared servers and monitor shared servers until you achieve ideal system performance for your typical database activity.

Estimating the Maximum Number of Dispatcher Processes

Use values for MAX_DISPATCHERS and DISPATCHERS that are at least equal to the maximum number of concurrent sessions divided by the number of connections per dispatcher. For most systems, a value of 1,000 connections per dispatcher provides good performance.

Disallowing Further Shared Server Use with Concurrent Shared Server Use

You can use the SET option of the ALTER SYSTEM statement to alter the number of active, shared servers. To prevent additional users from accessing shared servers, set SHARED_SERVERS to zero. This temporarily disables additional use of shared servers. Resetting SHARED_SERVERS to a positive value enables shared servers for all current users.

See Also:


Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index