Network Config   «Prev  Next»

Lesson 9 Contention in Shared Server
Objective Use the v$dispatcher view to identify contention in MTS.

Identify Contention via v$Dispatcher View

Can I use the "v$dispatcher view" to identify contention in Oracle Shared Server?
Absolutely! The `V$DISPATCHER` view is a valuable source of information for identifying contention issues within Oracle's Shared Server architecture. Here's how: **Key Metrics in V$DISPATCHER** * **BUSY:** Indicates the amount of time a dispatcher process has been occupied. High values here could signal that a dispatcher is overworked. * **IDLE:** Reflects the amount of time a dispatcher has been waiting for requests. * **STATUS:** Shows the current state of the dispatcher (e.g., "COMMON" indicates it's available for requests, "WAIT" might indicate it's waiting on some other resource). * **QUEUE:** The name of the request queue the dispatcher is assigned to. * **BYTES:** The number of bytes transferred by the dispatcher. **How to Analyze for Contention** 1. **Examine Dispatcher Utilization:** Compare the `BUSY` and `IDLE` columns. If dispatchers consistently show high values in the `BUSY` column with minimal idle time, it suggests they might be overloaded and a potential bottleneck. 2. **Check the Number of Waiting Requests:** Query the `V$QUEUE` view, which provides statistics about the request queues. Look for large numbers of waiting requests in a dispatcher's queue. This can highlight potential contention for that dispatcher. 3. **Correlate with Other Metrics:** Look at other dynamic performance views like `V$SYSTEM_EVENT` and `V$SESSION_WAIT` to identify waits related to shared server resources or other potential bottlenecks causing requests to back up at the dispatcher level. **Example Query:** ```sql SELECT NAME, NETWORK, STATUS, BYTES, IDLE, BUSY FROM V$DISPATCHER; ``` **Important Considerations:** * **Optimal Number of Dispatchers:** The number of dispatchers should generally align with the available CPU cores and the nature of your database workload. Too few or too many dispatchers can negatively impact performance. * **V$DISPATCHER_RATE:** This view gives you statistics on the dispatcher's processing rate (requests per second, bytes per second, etc.). Useful for comparing performance over time. **Let me know if you'd like a more advanced query or help with interpreting view results!**
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.

Activity of Dispatcher Processes

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"

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:

Oracle Integration Cloud Service
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.