/* ************************************************ */
/*                                                  */
/* Script: Display the busy rate for each dispatcher*/
/*         in the MTS environment                   */
/*                                                  */
/* name:    @c:\oracle\sql\v$dispat.sql             */
/* date:    28-AUG-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */

set pagesize 65;
set linesize 80;

/* ***************************** */
/* Busy rate for each dispatcher */
/* ***************************** */

select network 					"PROTOCOL",
       (SUM(busy)/(sum(busy) + sum(idle))) 	"TOTAL BUSY RATE"
from v$dispatcher
group by network;

/* If the dispatcher processes for a specific protocol are busy more      */
/*    then 50% then we can improve performance by adding more dispatchers */

/* ********************************** */
/* Monitor wait time for a dispatcher */
/* process response queues            */
/* ********************************** */

select network 					"PROTOCOL",
       decode(sum(totalq), 0, 'No responses',
	  sum(wait)/sum(totalq)||'hundereths of seconds')
						"Average Wait Time/request"
from v$dispatcher d,
	v$queue   q
where q.type = 'DISPATCHER' and
	q.paddr = d.paddr
group by network;

/* If the average wait time for a specific protocol continues to increase */
/*   steadily, you can improve performance by adding more dispatchers     */

commit;

