Program Connection Count (pgmcnt.sql)

What This Script Does

Counts database connections grouped by program name across RAC instances, showing the earliest and latest login times for each program type. This helps identify connection patterns and application usage across the database cluster.

The Script

rem  pgmcnt.sql
rem
ttitle 'Program Connection Count'
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
compute sum label SUM of cnt on report
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 program,inst_id, cnt desc
/

Usage

-- Show connection counts by program
@pgmcnt.sql

Parameters

The script requires no input parameters.

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$PROCESS
SELECT on GV$SESSION
SELECT on GV$SESS_IO

Sample Output

                                Program Connection Count

RAC PROGRAM NAME                                    MIN      MAX        Count
INS                                                 LOGON    LOGON
--- ----------------------------------------------- -------- -------- -------
  1 java.exe                                       06/01 08:30 06/01 14:25     145
  2 java.exe                                       06/01 08:35 06/01 14:20     132
  1 sqlplus.exe                                    06/01 09:00 06/01 13:45      23
  2 sqlplus.exe                                    06/01 09:15 06/01 13:50      18
  1 MyApplication.exe                              06/01 08:00 06/01 14:30      89
  2 MyApplication.exe                              06/01 08:05 06/01 14:28      76
  1 JDBC Thin Client                               06/01 07:30 06/01 14:45     234
  2 JDBC Thin Client                               06/01 07:35 06/01 14:40     198
                                                                        -------
SUM                                                                         915

Key Output Columns

RAC INST: RAC instance number
PROGRAM NAME: Name of the client program/application
MIN LOGON: Earliest login time for this program type
MAX LOGON: Latest login time for this program type
Count: Number of connections from this program

Program Types Commonly Seen

Development Tools

sqlplus.exe
toad.exe
sqldeveloper.exe

Application Clients

java.exe
JDBC Thin Client
JDBC OCI Client

Business Applications

MyApplication.exe
WebLogic Server
Apache Tomcat

Reporting Tools

excel.exe
Crystal Reports
Business Objects

Common Use Cases

Connection Pool Analysis

-- Monitor application connection patterns
@pgmcnt.sql
-- Analyze count distribution across instances

Load Balancing Assessment

-- Check connection distribution across RAC nodes
@pgmcnt.sql
-- Compare counts between instances

Application Usage Monitoring

-- Track different application usage levels
@pgmcnt.sql
-- Identify peak usage patterns from login times

Capacity Planning

-- Understand connection requirements by application
@pgmcnt.sql
-- Plan session limits and resource allocation

Security Auditing

-- Monitor connection sources and types
@pgmcnt.sql
-- Identify unexpected or unauthorized programs

Analysis Tips

Load Distribution

Compare counts between RAC instances
Uneven distribution may indicate load balancing issues

Connection Patterns

MIN/MAX LOGON times show usage windows
Persistent connections vs. short-lived sessions

Application Identification

Group similar program types for analysis
Identify connection pooling vs. direct connections

Filtering Logic

The script excludes Oracle background processes:

having program not like 'oracle%'

This focuses results on actual client connections rather than internal Oracle processes.

Performance Impact

Low Impact: Reads system views with minimal overhead
Aggregated Data: Groups results for easy analysis
RAC Optimized: Efficiently queries all instances

Troubleshooting

No Results

Verify client connections exist
Check if all connections are Oracle background processes

Unexpected Programs

Investigate unknown program names
May indicate unauthorized access or new applications

pgmcntm - Program count by module
pgmcnts - Program count by instance
pgmcntu - Program count by username
pgmcntu2 - Enhanced program count by username
gvsess - Active session analysis