| From: | Steve Adams |
| Date: | 16-May-2001 14:32 |
| Subject: | Tuning shared servers |
|
|
The dispatchers put client parse, execute, fetch (and so on) call requests into virtual circuit buffers. These buffers are then placed in the common queue from where they are picked up and serviced by a shared server. The WAIT column for the COMMON queue in V$QUEUE measures the time that requests spend waiting to be serviced, and you can calculate the average waiting time per request by dividing that number by the TOTALQ value. A small amount of waiting is normal because of IPC latencies, but if all the existing shared servers are busy you may be able to see client requests waiting in the COMMON queue in V$CIRCUIT.QUEUE. PMON monitors the common queue and the availability of shared servers every 3 seconds (by default) as part of its timeout actions and if necessary (as determined by the mysterious _mts_load_constants parameter) starts up additional shared servers up to the limit set by the mts_max_servers parameter. If the SERVERS_STARTED column of V$MTS stays at 0, then you can be confident that no client requests have waited for very long, and almost all of the common queue waiting time can be attributed to IPC latencies. This is the ideal. Otherwise, if the SERVERS_HIGHWATER value in V$MTS is less than mts_max_servers, then you should conclude that some requests have waited for PMON to start one or more additional shared servers and some of the common queue waiting time could have been avoided if the mts_servers parameter had been set higher. Nevertheless, this is acceptable. Worst of all if SERVERS_HIGHWATER is equal to mts_max_servers then requests have probably had to wait for other requests to complete before being able to be serviced. This also introduces risk of artificial deadlocks, and should be avoided. Apart from these factors, there is a good reason for trying to maintain a few idle shared servers. Namely, the probability of consecutive requests from the one client being serviced by the same shared server falls sharply as the shared server utilization rate approaches 100%. If so, session switching will be required for almost every call, and that introduces extra work and additional points of potential contention. Your V$SHARED_SERVER data below is not conclusive. There probably has been some common queue waiting time since the startup of the fourth shared server, but it is possible that there has not been. If there are some relatively long duration calls, that would increase the probability. You can get a histogram of the distribution of request service times in V$REQDIST. Ideally, almost all the requests should be in the first few buckets, and there should be very few outliers. Note however that almost any contention problem elsewhere in the instance (for example for enqueues, latches, buffer locks and so one) can cause long request times to be seen, and cause secondary performance problems with the MTS functionality. So once you've set a generous allowance for mts_servers you should set a defensive limit on mts_max_servers to prevent general performance problems from causing an unwanted blowout in the number of shared servers.
|
![]() |
SERVER STATUS MESSAGES IDLE BUSY BUSY_RATE REQUESTS ------ ---------------- ---------- ---------- ---------- --------- ---------- S000 WAIT(COMMON) 103579132 58775281 21423759 27 % 51789504 S001 EXEC 11377456 8803534 2954503 25 % 5688720 S002 WAIT(COMMON) 6460786 7689842 1871336 20 % 3230388 S003 WAIT(COMMON) 1170234 1092782 340891 24 % 585116Do these stats suggest to increase the number of server processes?
|