Shared server health check

prompt ***************************************
prompt Dispatcher detail usage
prompt ***************************************
prompt ( if time busy >50, then change MTS_MAX_DISPATCHERS in init.ora)
select name, status, idle, busy, busy/(busy+idle)*100 "Time Busy Rate" from v$dispatcher;

--select network "Protocol",
-- to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') "%Busy"
--from v$dispatcher
--group by network
--order by to_char((sum(busy)/(sum(busy)+sum(idle))*100), '99.9999') desc;

--Use the following statement to check for responses to user processes that are waiting in a queue to be sent to the user:
Prompt *************************************
Prompt Average wait time for response queue
Prompt *************************************
select Network Protocol,
Decode (Sum(Totalq), 0, 'No Responses',
Sum(Wait) / Sum(TotalQ) || ' hundredths of a second')
"Average Wait Time Per Response"
from V$Queue Q, V$Dispatcher D
where Q.Type = 'DISPATCHER'
and Q.Paddr = D.Paddr
group by Network;

--Use the following statement to check the requests from user processes that are waiting in a queue to be sent to the user:
Prompt *******************************************
Prompt All average wait time for common requests
Prompt *******************************************

select Decode (Sum(Totalq), 0, 'Number of Requests',
Sum(Wait) / Sum(TotalQ) || 'hundredths of a second')
"Average Wait Time Per Request"
from V$Queue
where Type = 'COMMON';