Use the V$QUEUE View to identify Contention on Oracle Shared Server

The `V$QUEUE` view in Oracle 19c is used to monitor the activity and potential contention on the shared server environment. It provides insights into the state of queues that are integral to Oracle's shared server architecture, such as the dispatcher and shared server message queues. To identify contention using the `V$QUEUE` view:
Steps:

1. Understand the Columns in V$QUEUE:
   • Key columns in V$QUEUE that can help you identify contention include:
     • TYPE: Specifies the type of queue (COMMON, DISPATCHER, SERVER).
     • PROCESSES: The number of processes waiting in the queue.
     • QUEUED: The number of messages in the queue.
     • WAIT: The time processes have spent waiting in the queue.
2. Query the View:
   • Use the following SQL query to examine the status of queues:

   • SELECT TYPE, PROCESSES, QUEUED, WAIT
     FROM V$QUEUE;
             

3. Interpret the Output:
   • High values in the QUEUED column may indicate contention as messages are piling up in the queue.
   • High values in the WAIT column suggest that processes are spending significant time waiting to be serviced, pointing to a bottleneck.
4. Focus on Specific Queues:
   • Dispatcher Queue Contention (TYPE = 'DISPATCHER'): High QUEUED and WAIT values might mean dispatchers cannot keep up with the incoming workload.
   • Shared Server Queue Contention (TYPE = 'SERVER'): High QUEUED and WAIT values indicate shared servers are struggling to process requests promptly.
5. Cross-Check with Related Views:
   • To gain a more comprehensive understanding, examine related views such as:
     • V$SHARED_SERVER: For information about shared server processes.
     • V$DISPATCHER: To monitor dispatchers and their associated workload.
     • V$DISPATCHER_RATE: For dispatch rate statistics.
6. Resolve Contention:
   • If contention is identified:
     • Increase Shared Servers: Adjust the SHARED_SERVERS parameter to add more shared server processes.
     • Add Dispatchers: Modify the DISPATCHERS parameter to handle more client connections.
     • Tune Applications: Reduce the workload or optimize SQL queries causing excessive demand.
     • Monitor Long-Running Queries: Use V$SESSION or V$SQL to identify and tune problematic sessions.
7. Regular Monitoring:
   • Consider setting up periodic monitoring of the V$QUEUE view and related views to proactively identify and address potential issues before they impact performance.

By analyzing the `V$QUEUE` data alongside other diagnostic tools, you can effectively pinpoint and mitigate contention in a shared server environment.

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 Dispatcher on shared server.
If so, examine the relevant parameters:

1. How many listeners are defined?
2. How many 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.