|Oracle9i Database Performance Guide and Reference
Release 1 (9.0.1)
Part Number A87503-02
This chapter contains the following topics:
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:
The following views provide dispatcher performance statistics:
V$DISPATCHER provides general information about dispatcher processes.
V$DISPATCHER_RATE view provides dispatcher processing statistics.
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
This section discusses how to add dispatcher processes and how to enable connection pooling.
Add dispatcher processes while Oracle is running with the
SET option of the
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.
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.
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:
This section discusses how to identify contention for shared servers and how to increase the maximum number of 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
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:
SELECT DECODE(TOTALQ, 0, 'No Requests', "AVERAGE WAIT TIME PER REQUESTS" FROM V$QUEUE WHERE TYPE = 'COMMON';
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:
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.
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_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.
You can also define starting values for the number of dispatchers and servers by setting the
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
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 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
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
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
SYSTEM statement. Experiment with this limit and monitor shared servers to determine an ideal setting for this parameter.
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"FROM V$SHARED_SERVER_MONITOR;
The output is:
MAXIMUM_CONNECTIONS SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER ------------------- --------------- ------------------ ----------------- 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.
Oracle9i Database Reference for complete information on
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
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:
SHARED_SERVERSshould be set for slightly greater than the expected number of shared servers that will be needed when the database is at an average load.
MAX_SHARED_SERVERSshould be set for slightly greater than the expected number of shared servers that will be needed when the database is at an peak load.
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.
Use values for
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.
You can use the
SET option of the
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.