Active Oracle Sessions with SQL and Wait Events (gvsessa.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

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

The Script

rem  vsessa.sql
rem
ttitle 'Active Oracle Sessions'
rem
set linesize 500
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 a28 heading 'MODULE/PROGRAM'
col last_call_et format 99,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   9999999999999999 HEADING  'SQL|Hash Value'
col sql_id heading 'SQL ID'
rem
select s.inst_id, s.sid, s.serial#, s.user#, s.username, s.osuser,
       s.module||'--'|| s.program module,
       -- s.command, s.taddr,
       s.lockwait, s.status,
       -- s.server,
       s.LAST_CALL_ET,
--       s.SQL_HASH_VALUE,
       s.SQL_ID, sw.event
  from gv$session s, gv$session_wait sw
 where s.inst_id like nvl('&inst_id','%')
   and s.sid like nvl('&sid','%')
   and s.sql_hash_value != 0
   and s.status='ACTIVE'
   and nvl(s.username,'null') like nvl(upper('&user'),'%')
   and s.sid = sw.sid
   and s.inst_id = sw.inst_id
 order by
       s.last_call_et desc,
       s.inst_id,
       s.sid
/

What This Script Does

This comprehensive script provides real-time monitoring of active Oracle sessions across all RAC instances, showing SQL execution details, wait events, and session characteristics. It’s essential for identifying active workload, troubleshooting performance issues, and understanding current database activity patterns.

Key Features

• RAC-Aware: Monitors sessions across all cluster instances
• Active Session Focus: Shows only currently active sessions
• SQL Integration: Displays currently executing SQL statements
• Wait Event Details: Shows what each session is waiting for
• Flexible Filtering: Filter by instance, session, or username
• Performance Metrics: Includes session runtime and elapsed time

Usage

Run the script and provide filter criteria when prompted:

@gvsessa.sql

Input Parameters:

• inst_id: RAC instance ID (specific number or % for all)
• sid: Session ID (specific SID or % for all)
• user: Username filter (specific user or % for all)

Required Privileges

SELECT on GV$SESSION
SELECT on GV$SESSION_WAIT

Sample Output

                                            Active Oracle Sessions

RAC                                   OS                        MODULE/PROGRAM               ELAPSED
INST  SID   SER  UID USERNAME     USERNAME LOCKWAIT STATUS     APP--PROG                   SECS      SQL ID         EVENT
---- ---- ----- --- ------------ -------- -------- -------- --------------------------- LAST CALL ------------- ----------------------
   1  156 23847  45 APPUSER      oracle            ACTIVE   MyApp--sqlplus.exe                 12 8mg7s5q2xpwnd db file sequential read
   1  298 45123  67 BATCHUSER    oracle            ACTIVE   BatchJob--java.exe                145 fnh7x2m9kqw8r log file sync
   2  189 12456  45 APPUSER      oracle   12ABC456 ACTIVE   MyApp--sqlplus.exe                456 2x9mfg4hj7pql enq: TX - row lock contention
   2  234 67890  89 REPORTUSER   oracle            ACTIVE   Crystal--crw32.exe                  8 5k9gh3x2mnbvf db file scattered read
   1  567 34521  23 WEBUSER      oracle            ACTIVE   Apache--httpd.exe                  23 7hjk9mn4x2bvq SQL*Net message from client

Key Output Columns

• RAC INST: RAC instance number
• SID: Session identifier
• SER: Serial number for session
• UID: User ID number
• USERNAME: Database username
• OS USERNAME: Operating system user
• LOCKWAIT: Address if session is waiting for a lock
• STATUS: Session status (should always be ACTIVE)
• MODULE/PROGRAM: Application module and program name
• ELAPSED SECS LAST CALL: Seconds since last call
• SQL ID: Currently executing SQL statement identifier
• EVENT: Current wait event

Understanding Session Activity

Session Status Analysis

• ACTIVE: Session is currently executing
• LOCKWAIT populated: Session is blocked waiting for a lock
• High ELAPSED SECS: Long-running operation or blocking

Wait Event Categories

I/O Related Waits

• db file sequential read: Index or single-block reads
• db file scattered read: Full table scans or multi-block reads
• direct path read: Direct path operations

Locking and Concurrency

• enq: TX - row lock contention: Row-level locking conflicts
• enq: TM - contention: Table-level lock conflicts
• buffer busy waits: Buffer cache contention

Network and Client

• SQL*Net message from client: Waiting for client response
• SQL*Net message to client: Sending data to client

Performance Analysis

Identifying Performance Issues

1. Long-Running Sessions

   -- Sessions running > 5 minutes
   ORDER BY s.last_call_et DESC

2. Blocking Sessions

   -- Sessions with lockwait addresses
   WHERE s.lockwait IS NOT NULL

3. Resource-Intensive Operations

   -- Look for patterns in SQL_ID and wait events

Common Problem Patterns

• Multiple sessions with same SQL_ID: Concurrent execution issues
• Sessions waiting on locks: Blocking/deadlock scenarios
• High I/O waits: Storage performance problems
• Network waits: Client-side or connectivity issues

Common Use Cases

1. Real-Time Performance Monitoring

   • Monitor active workload during peak periods
   • Identify resource-intensive operations
   • Track long-running jobs

2. Troubleshooting Session Issues

   • Find blocked sessions and their blockers
   • Identify sessions causing performance problems
   • Analyze wait event patterns

3. Capacity Planning

   • Understand concurrent session patterns
   • Identify peak usage times
   • Analyze application behavior

4. Application Monitoring

   • Track specific application sessions
   • Monitor batch job progress
   • Identify problematic SQL statements

Advanced Analysis

Finding Blocking Chains

-- After running gvsessa.sql, use lockwait addresses to find blockers
SELECT blocking_session, sid, serial#, username, sql_id
FROM gv$session
WHERE blocking_session IS NOT NULL;

SQL Statement Analysis

-- Get full SQL text for active SQL_IDs
SELECT sql_id, sql_text
FROM gv$sql
WHERE sql_id IN ('8mg7s5q2xpwnd', 'fnh7x2m9kqw8r');

Wait Event Deep Dive

-- Get detailed wait information
SELECT sid, event, wait_class, seconds_in_wait, state
FROM gv$session_wait
WHERE sid IN (156, 298, 189);

Filtering Examples

Monitor Specific Application

-- Filter for specific application users
Enter value for inst_id: %
Enter value for sid: %
Enter value for user: APPUSER

Monitor Specific Instance

-- Focus on instance 1 only
Enter value for inst_id: 1
Enter value for sid: %
Enter value for user: %

Monitor Long-Running Sessions

-- Results automatically ordered by elapsed time
-- Look at top entries for longest-running sessions

Troubleshooting

No Results Returned

• No active sessions match the filter criteria
• All sessions may be idle
• Check if database is receiving connections

Too Many Results

• Use more specific filters (username, instance)
• Focus on sessions with high elapsed time
• Filter for specific wait events of interest

Performance Impact

• Script queries dynamic performance views
• Consider running during lower activity periods
• Use specific filters to reduce result set

Related Scripts

• gvsess.sql - Basic active session analysis
• gvsessab.sql - Session activity breakdown
• vsession.sql - Single instance session details
• gvlock.sql - Lock analysis across RAC