Explore your Database Server
If you have access to an Oracle database server, you might find it both interesting and useful to enter the data dictionary and run the
v$dispatcher
and
v$queue
queries from the previous two lessons. Note in the returns to the queries whether your DBA has
defined the MTS. If so, examine the relevant parameters:
- How many listeners are defined?
- How many MTS dispatchers are defined for each instance?
- How many server processes are defined for each instance?
- What are the v$dispatcher statistics? Are they reasonable?
- How are the times for the response queues?
Identifying Contention Using the Dispatcher-specific Views
The following views provide dispatcher performance statistics:
- V$DISPATCHER
- V$DISPATCHER_RATE
V$DISPATCHER provides general information about dispatcher processes.
V$DISPATCHER_RATE view provides dispatcher processing 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 "MAX_" prefixes 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, you likely have an optimally tuned MTS
environment.
If the current and average rates are significantly below the maximums, consider reducing the number of dispatchers. Conversely, if current and average rates are
close to the maximums, you may need to add more dispatchers. A good rule-of-thumb is to examine V$DISPATCHER_RATE statistics during both light and
heavy system use periods. After identifying your MTS 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.