RelationalDBDesign RelationalDBDesign


Network Config   «Prev 

v$ views

Remember, the v$ views collect information since system startup time. You cannot simply look at statistics from the time the instance started; rather, you must check statistics relevant to the workload you are applying. Thus, if the dispatcher processes for TCP is busy more than 50% since startup time, there are probably times when it is 100% busy, and times when it is not busy. In any case, if busy rates approach 50%, you will probably benefit by adding another dispatcher process.

Oracle - Identifying Contention in the MTS

Contention for dispatcher processes can be reflected either by
  1. high busy rates for existing dispatcher processes, or
  2. by a steady increase in waiting time for responses in the response queues of existing dispatcher processes.

  1. v$dispatcher and
  2. v$queue views
can help us identify these conditions.
The v$dispatcher view contains statistics reflecting the activity of 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. The IDLE and BUSY columns reflect busy rates for dispatcher processes:
Use the following query to monitor these statistics for a period 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 for the dispatcher processes of each protocol; that is, the percentage of time the dispatcher processes of each protocol are busy. The result of this query might look like this:
Protocol  Total Busy Rate
 --------  ---------------
 tcp            .5053635
 spx            .1039372
 

From this result, you can observe that the spx dispatcher processes are busy 10% of the time and the tcp dispatcher processes are busy 50% of the time. (since database startup time)
The v$ views collect information since system startup time. You cannot simply look at statistics from the time the instance started; rather, you must check statistics relevant to the workload you are applying. Thus, if the dispatcher processes for tcp is busy more than 50% since startup time, there are probably times when it is 100% busy, and times when it is not busy. In any case, if busy rates approach 50%, you will probably benefit by adding another dispatcher process.

The v$queue view contains statistics reflecting the response queue activity for 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. The WAIT and TOTALQ columns show wait times for responses in the queue. To see the data, you can use the following query to monitor these statistics occasionally while your application is running:

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 returns the average time that a response waits in each response queue for a dispatcher process to route it to a user process. This query uses the V$DISPATCHER table to group the rows of the V$QUEUE table by network protocol. The query also 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

From this result, you can tell 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. 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 using that protocol.
If you encounter problems with the MTS, you can quickly regress to dedicated servers by issuing an ALTER SYSTEM command. The following command turns off the MTS by setting the number of MTS servers to zero:
SVRMGR> ALTER SYSTEM SET MTS_SERVERS=0;