RelationalDBDesign RelationalDBDesign


Network Config   «Prev  Next»
Lesson 10 Contention in MTS, part 2
Objective Use the v$queue View to identify Contention in MTS.

Use the v$queue View to identify Contention in Oracle MTS

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:

SVRMGR> 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.
Explore Database Server
The next lesson concludes this module.