SQL Script MTS Monitor Script

Updated: 27-May-2003
Version: 1.0

Description

Lists difference aspects of MTS monitoring.

Parameters

None.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


col network form a60
col program form a20

spool mts.lst

PROMPT MTS - map dispatchers to processes

select d.network network, d.name disp , s.username, s.sid,
s.serial# , p.username , p.terminal, s.program
from v$dispatcher d, v$circuit c, v$session s, v$process p 
where d.paddr = c.dispatcher (+) 
and c.saddr = s.saddr (+) 
and s.paddr = p.addr(+) 
order by d.network, d.name, s.username
/
 
PROMPT MTS - average wait times per request

SELECT DECODE (
          totalq,
          0, 'No Requests',
           (wait / totalq) * 100 || ' Seconds Request Wait'
       ) awt
  FROM v$queue
 WHERE type = 'COMMON'
/

PROMPT MTS - average wait time per response

SELECT network
     , 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 
/

PROMPT % Dispatchers Busy

select network, sum(busy),sum(idle),100 * sum(busy)/(sum(busy)+sum(idle)) busy 
from v$dispatcher 
group by network
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links