Session Activity Analysis (gvsessab.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

Analyzes active Oracle sessions that are currently executing SQL statements, filtering for sessions with meaningful activity (elapsed time > 5 seconds and non-zero SQL hash values). This provides a focused view of sessions that may need attention.

The Script

rem  gvsessab.sql
rem
ttitle 'Active Oracle Sessions'
rem
set linesize 132
rem
col inst_id format 99 heading 'RAC|INST'
col sid format 9990 heading 'SID'
col serial# format 99990 heading 'SER'
col user# format 990 heading 'UID'
col username format a12 heading 'USERNAME'
col osuser format a8 heading 'OS|USERNAME'
col command format 990 heading 'COMMAND'
col taddr format a8 heading 'TADDR'
col lockwait format a8 heading 'LOCKWAIT'
col status format a8 heading 'STATUS'
col server format a9 heading 'SERVER'
col module format a22 heading 'MODULE/PROGRAM'
col last_call_et format 999,999 heading 'ELAPSED|SECS|LAST|CALL'
col failover_type format a9 heading 'FAILOVER|TYPE'
col failover_mode format a9 heading 'FAILOVER|METHOD'
col failed_over format a6 heading 'FAILED'
col sql_hash_value FORMAT   99999999999999 HEADING  'SQL|Hash Value'
col sql_id FORMAT   a15 HEADING  'SQL ID'
rem
select inst_id, sid, serial#, user#, username, osuser,
       module||'--'|| program module,
       lockwait, status,
       LAST_CALL_ET,
       SQL_HASH_VALUE,
       SQL_ID
  from gv$session
 where inst_id like nvl('&inst_id','%')
   and sid like nvl('&sid','%')
   and sql_hash_value != 0
   and status='ACTIVE'
   and LAST_CALL_ET > 5
 order by
       last_call_et desc,
       inst_id,
       sid
/

Usage

-- Basic usage - show active sessions with SQL activity
@gvsessab.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all sessions]

-- Filter by specific RAC instance
@gvsessab.sql
-- Enter: 1 (for instance 1)

-- Filter by specific session
@gvsessab.sql
-- Enter: [press enter for all instances]
-- Enter: 1234 (for specific SID)

Parameters

The script prompts for:

• inst_id: RAC instance ID (% for all instances)
• sid: Session ID (% for all sessions)

Required Privileges

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

Sample Output

                                Active Oracle Sessions

RAC  SID   SER UID USERNAME     OS       MODULE/PROGRAM         LOCKWAIT STATUS   ELAPSED  SQL Hash Value SQL ID
INS              #              USERNAME                                         SECS
--- ---- ----- --- ------------ -------- ---------------------- -------- -------- ------- -------------- ---------------
  1 1234  5678 102 SCOTT       oracle   sqlplus.exe--sqlplus.e          ACTIVE       120  1234567890123 abc123def456789
  2 2345  6789  98 HR_USER     webapp   JDBC Thin Client--java.         ACTIVE        67   987654321098 def456ghi789abc
  1 3456  7890 105 SALES_USER  java     MyApp--MyApplication.j          ACTIVE        45   567890123456 ghi789jkl012def

Key Output Columns

• RAC INST: RAC instance number
• SID: Session identifier
• SER #: Serial number
• UID: User ID number
• USERNAME: Database username
• OS USERNAME: Operating system username
• MODULE/PROGRAM: Application module and program name
• LOCKWAIT: Lock wait indicator
• STATUS: Session status (filtered to ACTIVE only)
• ELAPSED SECS LAST CALL: Seconds since last activity
• SQL Hash Value: Hash value of current SQL statement
• SQL ID: Current SQL statement identifier

Filtering Logic

The script applies several filters to focus on relevant sessions:

• sql_hash_value != 0: Sessions with actual SQL statements
• status=‘ACTIVE’: Only active sessions
• LAST_CALL_ET > 5: Sessions idle for more than 5 seconds

Common Use Cases

Long-Running Query Detection

-- Identify sessions with long-running SQL
@gvsessab.sql
-- Look for sessions with high ELAPSED SECS values

Active SQL Monitoring

-- Monitor sessions currently executing SQL
@gvsessab.sql
-- Focus on sessions with non-zero SQL hash values

Performance Bottleneck Analysis

-- Find sessions that may be experiencing issues
@gvsessab.sql
-- Analyze sessions with extended elapsed times

RAC Load Analysis

-- Check SQL activity distribution across instances
@gvsessab.sql
-- Compare activity levels between RAC nodes

Performance Impact

• Low Impact: Simple query on GV$SESSION with efficient filters
• Focused Results: Filters reduce output to relevant sessions only
• RAC Optimized: Efficiently queries across all instances

Related Scripts

• gvsess - Comprehensive active session analysis
• gvtrans - Transaction analysis
• sqltext - SQL text retrieval by SQL_ID
• gvlock - Lock analysis