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.
Use this query to monitor these statistics 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 (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:
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.