Identify Contention via v$Dispatcher View

To identify contention on a Shared Server configuration in Oracle 19c using the `V$DISPATCHER` view, follow these steps:
Purpose of `V$DISPATCHER`
The `V$DISPATCHER` view provides detailed runtime statistics about the dispatcher processes in a Shared Server architecture. These statistics help identify contention and performance issues.
Key Columns in `V$DISPATCHER`
Here are some important columns in `V$DISPATCHER` that can help analyze contention:

1. NAME: Name of the dispatcher (e.g., D000).
2. NETWORK: Network protocol used by the dispatcher.
3. BUSY: Time (in hundredths of a second) that the dispatcher was busy servicing requests.
4. IDLE: Time (in hundredths of a second) that the dispatcher was idle.
5. MESSAGES: Total number of messages processed by the dispatcher.
6. BYTES: Total number of bytes processed by the dispatcher.
7. QUEUED: Number of requests currently waiting in the dispatcher's queue.
8. WAIT: Time spent waiting for requests.

Steps to Identify Contention

1. Check Dispatcher Queues:
   • Use the QUEUED column to identify dispatchers with a high number of waiting requests.
   • Example Query:

     SELECT NAME, NETWORK, BUSY, IDLE, QUEUED, WAIT, MESSAGES, BYTES
     FROM V$DISPATCHER
     WHERE QUEUED > 0;
             

   • A high value in the QUEUED column indicates that requests are waiting for dispatchers to process them, which suggests contention.
2. Calculate Dispatcher Load:
   • Compare the BUSY and IDLE times of each dispatcher to assess how busy they are.
   • Example Query:
     

     SELECT NAME, 
              (BUSY / (BUSY + IDLE)) * 100 AS BUSY_PERCENTAGE,
              QUEUED
     FROM V$DISPATCHER;
             

   • If the busy percentage is very high and the QUEUED value is non-zero, you might need to add more dispatchers or Shared Server processes.
3. Analyze Wait Times:
   • A high WAIT value for a dispatcher can indicate contention or slow responses to user requests.
4. Combine with V$QUEUE:
   • The V$QUEUE view provides more detailed queue statistics for each dispatcher.
   • Example Query:
     

     SELECT * 
     FROM V$QUEUE 
     WHERE TYPE = 'DISPATCHER';
     

   • Look for high CURRENT_UTILIZATION or long WAIT_TIME.
5. Correlate with User Sessions:
   • Use V$SESSION to determine which sessions are using shared servers and correlate them with dispatcher contention.
   • Example Query:

     SELECT S.SID, S.USERNAME, S.SERVER, S.STATUS
     FROM V$SESSION S
     WHERE S.SERVER = 'SHARED';
             

Actions Based on Observations

1. High Dispatcher Queue (QUEUED):
   • Increase the number of dispatcher processes using the DISPATCHERS initialization parameter.
   • Example:

     ALTER SYSTEM SET DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=4)';
             

2. High Busy Percentage (BUSY_PERCENTAGE):
   • Increase the number of Shared Servers using the MAX_SHARED_SERVERS parameter.
3. Persistent Waits (WAIT):
   • Tune the database network or investigate specific user sessions causing excessive load.

By monitoring the `V$DISPATCHER` view alongside related views like `V$QUEUE` and `V$SESSION`, you can effectively diagnose and address contention issues in a Shared Server environment.

Contention is a term that is used to describe many resources that are competing for a single database resource. Contention for dispatcher processes may be reflected either in high busy rates for existing dispatcher processes or in a steady increase in response time in the response queues of existing dispatcher processes. The v$dispatcher and v$queue views can help track these conditions. The v$ views collect information from the moment the system starts up.

The v$dispatcher view displays statistics reflecting the activity of dispatcher processes.

Use this query to monitor these statistics while your application is running:

SELECT network                               "Protocol",
      SUM(busy) / ( SUM(busy) + SUM(idle) )  "Total Busy Rate"
FROM 
   v$dispatcher
GROUP BY 
   network;

This query returns the total busy rate (the percentage of time the dispatcher processes of each protocol are busy) for the dispatcher processes of each protocol. (The IDLE and BUSY columns reflect busy rates for dispatcher processes.) The result of this query might look like this:

Protocol  Total Busy Rate
--------  ---------------
tcp            .5053635
spx            .0939372

This tells you that since database startup, the spx dispatcher processes have been busy 9 percent of the time and the TCP dispatcher processes have been busy 50 percent of the time.
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.
The next lesson examines the v$queue view.