| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
| Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02 |
|
|
View PDF |
Proper configuration of shared servers can result in significant performance improvement.
This chapter contains the following topic:
Using shared servers enables you to reduce the number of processes and the amount of memory consumed on the server machine. Shared servers are beneficial for systems where there are many OLTP users performing intermittent transactions.
Using shared servers rather than dedicated servers is also generally better for systems that have a high connection rate to the database. With shared servers, when a connect request is received, a dispatcher is already available to handle concurrent connection requests. With dedicated servers, on the other hand, a connection-specific dedicated server is sequentially initialized for each connection request.
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, a session can be prevented from migrating to another shared server while parallel execution is active.
A session can remain nonmigratable even after a request from the client has been processed, because not all the user information has been stored in the UGA. If a server were to process the request from the client, then the part of the user state that was not stored in the UGA would be inaccessible. To avoid this, individual shared servers often need to remain bound to a user session.
When using some features, you may need to configure more shared servers, 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 - general information about dispatcher processesV$DISPATCHER_RATE - dispatcher processing statistics
See Also:
|
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 sample. 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 less than the maximum, then you likely have an optimally tuned shared server environment.
If the current and average rates are significantly less than 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.
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 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 |
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:
DISPATCHERS = "(PROTOCOL=TCP)(POOL=ON)"
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 1 second.
| See Also:
Oracle9i Database Reference and the Oracle9i Net Services Administrator's Guide for more information about the |
Multiplexing is used by a connection manager process to establish and maintain network sessions 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, session multiplexing is beneficial because it maximizes use of the dispatcher process connections.
Multiplexing is also useful for multiplexing database link sessions between dispatchers. The limit on the number of sessions for each dispatcher is platform dependent. For example:
DISPATCHERS="(PROTOCOL=TCP)(MULTIPLEX=ON)"
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 SELECT ANY TABLE system privilege, such as SYSTEM. Table 19-1 lists the columns showing the wait times for requests and the number of requests in the queue.
Monitor these statistics occasionally while your application is running by issuing the following SQL statement:
SELECT DECODE(TOTALQ, 0, 'No Requests', WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS')"AVERAGE WAIT TIME PER REQUESTS" FROM V$QUEUE WHERE TYPE = 'COMMON';
This query returns the results of a calculation that show the following:
AVERAGE WAIT TIME PER REQUEST ----------------------------- .090909 HUNDREDTHS OF SECONDS
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" FROM V$SHARED_SERVER WHERE STATUS != 'QUIT';
The result of this query could look like the following:
Shared Server Processes ----------------------- 10
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 in the following section.
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.
|
Note: Setting |
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.
When the database starts, SHARED_SERVERS is the number of shared servers created. Oracle does not allow the number of shared servers to be less than 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.
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 ideal 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 want to fix the number of shared server processes. Keep in mind the following rules:
SHARED_SERVERS should 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_SERVERS should be set for slightly greater than the expected number of shared servers that will be needed when the database is at an peak load.As mentioned, SHARED_SERVERS determines the number of shared servers activated at instance startup. The default setting for SERVER_SERVERS is one 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 MAX_DISPATCHERS and DISPATCHERS that are at least equal to the maximum number of concurrent sessions divided by the number of connections for each dispatcher. For most systems, a value of 1,000 connections for each dispatcher provides good performance.
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.
To prevent a dispatcher from being used, issue the statement ALTER SYSTEM SHUTDOWN [IMMEDIATE]. To bring the dispatcher back online, use the ALTER SYSTEM SET command for the DISPATCHERS parameter.
See Also:
|
|
 Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|