RelationalDBDesign RelationalDBDesign

Network Config   «Prev  Next»
Lesson 9 Contention in MTS, part 1
Objective Use the v$dispatcher view to identify contention in MTS.

Identify Contention via v$Dispatcher View

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.