Skip to content
Oracle DBA Scripts & Database Utilities

Session Count Summary by Machine and User (gvsessactive.sql)

What This Script Does

Section titled “What This Script Does”

This script provides Oracle database functionality via the gvsessactive.sql script.

The Script

Section titled “The Script”
col machine form a20
col module form a20
col active form 999
col inactive form 999,999
break on report skip 1 on machine skip 1 on osuser skip 1 on username skip 1 on module skip 1
compute sum of count(*) active inactive on machine report
select
       s.machine,
       s.osuser,
       s.username,
       s.module,
       s.inst_id,
       sum(decode(s.status, 'ACTIVE', 1, 0)) active,
       sum(decode(s.status, 'ACTIVE', 0, 1)) inactive,
       count(*)
  from gv$session s
where type != 'BACKGROUND'
group by
       s.machine,
       s.osuser,
       s.username,
       s.module,
       s.inst_id
order by 1,2,3,4,5
/

What This Script Does

Section titled “What This Script Does”

This script provides a comprehensive session summary report that aggregates all user sessions (non-background) by machine, OS user, database username, module, and RAC instance. It shows the breakdown between active and inactive sessions, making it invaluable for capacity planning, connection pool analysis, and understanding application connectivity patterns.

Key Features

Section titled “Key Features”
  • Hierarchical Grouping: Organized by machine → OS user → DB user → module → instance
  • Session State Analysis: Separates active from inactive sessions
  • RAC-Aware: Shows distribution across cluster instances
  • Connection Pool Insight: Identifies connection patterns by application
  • Capacity Planning: Helps understand session usage patterns
  • Clean Reporting: Professional break formatting with subtotals

Usage

Section titled “Usage”
@gvsessactive.sql

No parameters required - analyzes all current user sessions.

Required Privileges

Section titled “Required Privileges”
SELECT on GV$SESSION

Sample Output

Section titled “Sample Output”
MACHINE              OSUSER   USERNAME     MODULE               INST_ID ACTIVE INACTIVE   COUNT(*)
-------------------- -------- ------------ -------------------- ------- ------ -------- ----------
appserver01          oracle   APPUSER      MyApplication              1      5       15         20
                                                                       2      3       12         15
                              *****                                           8       27         35
                     *****                                                    8       27         35


appserver02          oracle   APPUSER      MyApplication              1      2       18         20
                                                                       2      1       14         15
                              *****                                           3       32         35
                     *****                                                    3       32         35


batchserver01        oracle   BATCHUSER    BatchProcessor             1      1        4          5
                                                                       2      0        5          5
                              *****                                           1        9         10
                     *****                                                    1        9         10


webserver01          apache   WEBUSER      Apache                     1     12        8         20
                              WEBUSER      mod_plsql                  1      3        2          5
                              *****                                          15       10         25
                     *****                                                   15       10         25


workstation01        jsmith   JSMITH       SQL*Plus                   1      0        2          2
                              JSMITH       Toad                       1      1        0          1
                              *****                                           1        2          3
                     *****                                                    1        2          3


                                                                            ====== ======== ==========
sum                                                                            28       80        108

Key Output Columns

Section titled “Key Output Columns”
  • MACHINE: Client machine name or IP address
  • OSUSER: Operating system username
  • USERNAME: Oracle database username
  • MODULE: Application module or program name
  • INST_ID: RAC instance number
  • ACTIVE: Number of sessions currently executing
  • INACTIVE: Number of idle/waiting sessions
  • COUNT(*): Total sessions for this grouping

Understanding Session Patterns

Section titled “Understanding Session Patterns”

Connection Pool Analysis

Section titled “Connection Pool Analysis”
  • High Inactive/Low Active: Indicates connection pooling or idle connections
  • Many Connections per Machine: May suggest connection pool sizing issues
  • Consistent Patterns: Shows well-configured connection management

Application Behavior

Section titled “Application Behavior”
  • Module Identification: Shows which applications are connecting
  • User Distribution: Reveals how users connect to the database
  • Load Balancing: Instance distribution shows RAC load balancing

Capacity Planning Insights

Section titled “Capacity Planning Insights”
  • Peak Connection Counts: Maximum concurrent connections per application
  • Session Utilization: Active vs inactive ratio indicates efficiency
  • Resource Planning: Helps size connection pools and session limits

Performance Analysis

Section titled “Performance Analysis”

Connection Pool Efficiency

Section titled “Connection Pool Efficiency”
-- Calculate connection pool efficiency
Efficiency = Active Sessions / Total Sessions * 100


-- High efficiency (>70%): Well-tuned pool
-- Low efficiency (<30%): Over-provisioned connections

Load Distribution

Section titled “Load Distribution”
-- Even distribution across instances indicates good load balancing
-- Uneven distribution may suggest connection string issues

Application Patterns

Section titled “Application Patterns”
-- Multiple modules per user: Multi-tier applications
-- Single module per user: Simple client-server applications
-- Mixed patterns: Complex application architecture

Common Use Cases

Section titled “Common Use Cases”

  1. Connection Pool Tuning

    • Identify over-allocated connection pools
    • Find applications with too many idle connections
    • Optimize connection pool sizes

  2. Capacity Planning

    • Understand peak session requirements
    • Plan for session limit increases
    • Size shared server configurations

  3. Application Monitoring

    • Monitor application connectivity patterns
    • Identify unusual connection behavior
    • Track application deployment impact

  4. Performance Troubleshooting

    • Find applications consuming excessive connections
    • Identify connection leaks
    • Analyze session distribution issues

  5. Security Auditing

    • Monitor user connection patterns
    • Identify unexpected connections
    • Track application access patterns

Advanced Analysis

Section titled “Advanced Analysis”

Connection Pool Health Check

Section titled “Connection Pool Health Check”
-- After running the script, calculate ratios:
-- If Active/Total < 0.2: Connection pool may be oversized
-- If Active/Total > 0.8: Connection pool may be undersized
-- Optimal range: 0.3-0.7 depending on application

Load Balancing Assessment

Section titled “Load Balancing Assessment”
-- Check instance distribution:
-- Even distribution = good load balancing
-- Skewed distribution = connection string or load balancer issues

Application Efficiency Metrics

Section titled “Application Efficiency Metrics”
-- Sessions per application server:
-- High counts per machine = potential connection pooling issues
-- Low utilization = optimization opportunities

Troubleshooting Common Issues

Section titled “Troubleshooting Common Issues”

Too Many Idle Connections

Section titled “Too Many Idle Connections”
  • Symptoms: High inactive counts, low active counts
  • Causes: Connection pool misconfiguration, connection leaks
  • Solutions: Tune pool sizes, implement connection timeouts

Uneven RAC Distribution

Section titled “Uneven RAC Distribution”
  • Symptoms: Sessions concentrated on one instance
  • Causes: Connection string issues, load balancer problems
  • Solutions: Check TAF configuration, verify load balancer settings

Unexpected Connection Sources

Section titled “Unexpected Connection Sources”
  • Symptoms: Unknown machines or users in results
  • Causes: Unauthorized access, application misconfiguration
  • Solutions: Review security, audit application connections

Script Customization

Section titled “Script Customization”

Add Time-Based Filtering

Section titled “Add Time-Based Filtering”
-- Add logon time analysis
select s.machine, s.osuser, s.username, s.module,
       trunc(s.logon_time) logon_date,
       sum(decode(s.status, 'ACTIVE', 1, 0)) active,
       sum(decode(s.status, 'ACTIVE', 0, 1)) inactive,
       count(*)
from gv$session s
where type != 'BACKGROUND'
and s.logon_time > sysdate - 1  -- Last 24 hours
group by s.machine, s.osuser, s.username, s.module, trunc(s.logon_time);

Focus on Specific Applications

Section titled “Focus on Specific Applications”
-- Filter for specific modules
where type != 'BACKGROUND'
and s.module like 'MyApp%'
Section titled “Related Scripts”