Use the v$queue View to identify Contention in Oracle Shared Server

The v$queue view displays statistics reflecting activity in the response queue for dispatcher processes. You can use the following query to monitor these statistics while your application is running.
View the code below to view the query.

SELECT network "Protocol",
       DECODE( SUM(totalq), 0, 'No Responses',
       SUM(wait)/SUM(totalq) || ' hundredths of seconds')
      "Average Wait Time per Response"
FROM 
   v$queue q, 
   v$dispatcher d
WHERE 
   q.type = 'DISPATCHER'
   AND 
   q.paddr = d.paddr
GROUP BY network;

This query:

1. Returns the average time that a response waits in each response queue before a dispatcher process routes it to a user process
2. Uses the V$DISPATCHER table to group the rows of the V$QUEUE table by network protocol
3. Uses the DECODE syntax to recognize those protocols for which there have been no responses in the queue

The result of this query might look like this:

Protocol  Average Wait Time per Response
--------  ------------------------------
spx      .1739130 hundredths of seconds
tcp       No Responses

This result indicates that a response in the queue for spx dispatcher processes waits an average of 0.17 hundredths of a second and that there have been no responses in the queue for TCP dispatcher processes. By default, this table is available only to the user SYS and to other users who have SELECT ANY TABLE system privilege, such as SYSTEM. If you encounter problems with MTS, you can quickly regress to dedicated servers by issuing an ALTER SYSTEM command. The following command turns off MTS by setting the number of MTS servers to zero:

SQL> ALTER SYSTEM SET MTS_SERVERS=0;

If the average wait time for a specific network protocol continues to increase steadily as your application runs, then by adding dispatcher processes you may be able to improve performance of those user processes connected to Oracle that are using that protocol. If you have access to a Oracle database, click on the following link to 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:

1. How many listeners are defined?
2. How many MTS dispatchers are defined for each instance?
3. How many server processes are defined for each instance?
4. What are the v$dispatcher statistics? Are they reasonable?
5. How are the times for the response queues?

The following views provide dispatcher performance statistics:

1. V$DISPATCHER
2. 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.
The next lesson concludes this module.