Skip to content

Program Connection Count by Module (pgmcntm.sql)

What This Script Does

Counts database connections by program name with breakdown by RAC instance, showing connection patterns and load distribution across the cluster.

The Script

rem  pgmcntm.sql
rem
ttitle 'Program Connection Count by Module'
rem
clear breaks
clear computes
rem
set lines 132
set pagesize 80
rem
col inst_id format 99 heading 'RAC|INST'
col program format a48 heading 'PROGRAM NAME'
col module format a13 heading 'MODULE'
col min_logon format a12 heading 'MIN|LOGON'
col max_logon format a12 heading 'MAX|LOGON'
col cnt format 999,999 heading 'Count'
rem
break on report on inst_id skip 1
compute sum label SUM of cnt on report inst_id
rem
select inst_id
     , program
     , to_char(min(to_date(logon_time,'yyyy/mm/dd hh24:mi')),'mm/dd hh24:mi') min_logon
     , to_char(max(to_date(logon_time,'yyyy/mm/dd hh24:mi')),'mm/dd hh24:mi') max_logon
     , count(*) cnt
from
(
select s.inst_id,
       s.program,
       s.module,
       to_char( s.logon_time, 'yyyy/mm/dd hh24:mi' ) logon_time
  from gv$process p, gv$session s, gv$sess_io i
 where i.sid = s.sid
   and i.inst_id = s.inst_id
   and s.inst_id = p.inst_id(+)
   and s.paddr = p.addr(+)
)
group by inst_id, program
having program not like 'oracle%'
order by inst_id, program, cnt desc
/

Usage

-- Show connection counts grouped by instance
@pgmcntm.sql

pgmcnt - Basic program connection count
pgmcntu - Program count by username