| IDLE | This column shows the idle time for the dispatcher process in hundredths of seconds. |
| BUSY | This column shows the busy time for the dispatcher process in hundredths of seconds. |
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 or 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
-------- ---------------
decnet .004589828
tcp .029111042
From this result, you can make these observations:
Examining Wait Times for Dispatcher Process Response Queues Statistics reflecting the response queue activity for dispatcher processes are kept in the dynamic performance table V$QUEUE. By default, this table is only available to the user SYS and to other users who have SELECT ANY TABLE system privilege, such as SYSTEM. These columns show wait times for responses in the queue:
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 in hundredths of seconds 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
-------- ------------------------------
decnet .1739130 hundredths of seconds
tcp No Responses
From this result, you can tell that a response in the queue for DECNET 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, you may be able to improve performance of those user processes connected to Oracle using that protocol by adding dispatcher processes.
For more information on adding dispatcher processes, see the Oracle7 Server Administrator's Guide.
The total number of dispatcher processes across all protocols is limited by the value of the initialization parameter MTS_MAX_DISPATCHERS. You may need to increase this value before adding dispatcher processes. The default value of this parameter is 5 and the maximum value varies depending on your operating system.
SELECT DECODE( totalq, 0, 'No Requests',
wait/totalq || ' hundredths of seconds')
"Average Wait Time Per Requests"
FROM v$queue
WHERE type = 'COMMON';
This query returns the total wait time for all requests and total number of requests for the request queue. The result of this query might look like this:
Average Wait Time per Request
-----------------------------
.090909 hundredths of seconds
From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue before it is processed.
You can also determine how many shared server processes are currently running by issuing this query:
SELECT COUNT(*) "Shared Server Processes"
FROM v$shared_servers
WHERE status != 'QUIT';
The result of this query might look like this:
Shared Server Processes
-----------------------
10