Global Session Events Analysis for Business Objects (gvsessev9_bo.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 comprehensive analysis of session wait events across all RAC instances specifically for Business Objects (BO) sessions. It monitors performance metrics for sessions connected with the ‘BO_PAL’ username and ‘boadmtst’ OS user, filtering out SQL*Net events to focus on database-level performance issues. The script helps identify bottlenecks, performance degradation, and system resource contention affecting BO applications.

The Script

rem  gvsessev.sql
rem
ttitle 'Global Session Events'
rem
set linesize 132
rem
col inst_id format 999 heading 'INST'
col s_sid format 9999 heading 'SESS|SID'
col e_sid format 9999 heading 'EVNT|SID'
col username format a10 heading 'USERNAME'
col elapsed_secs format 9999999 heading 'ELAPSED|SECS'
col event format a28 heading 'EVENT'
col total_waits format 99999999 heading 'TOTAL|WAITS'
col total_timeouts format b9999999 heading 'TOTAL|TIMEOUTS'
col time_waited_secs format b9999999.00 heading 'TIME|WAITED|(secs)'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b999999999 heading 'TIME|WAITED'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
col avg_wait_ms format b9999.0 heading 'AVERAGE|WAIT|(ms)'
col MAX_WAIT format b999999.9 heading 'MAX|WAIT'
col MAX_WAIT_MS format b999999 heading 'MAX|WAIT|(ms)'
COLUMN   module HEADING  'MODULE' FORMAT   a29 trunc
rem
break on report on inst_id on s_sid on username on elapsed_secs skip 1
compute sum of total_waits total_timeouts time_waited_secs on report s_sid
rem
select
       s.inst_id,
       s.sid s_sid,
       -- e.sid e_sid,
       -- s.module,
       s.username,
       (sysdate - s.logon_time) * 24 *60 *60 elapsed_secs,
       e.event,
       e.total_waits,
       e.total_timeouts,
       100 * e.total_timeouts / e.total_waits timeout_pct,
       e.time_waited_micro / 1000000 time_waited_secs,
       e.MAX_WAIT * 10 max_wait_ms,
       e.time_waited_micro / e.total_waits / 1000 avg_wait_ms
  from gv$session_event e, gv$session s
 where s.inst_id like nvl( '&inst_id', '%')
   and s.sid like nvl( '&sid', '%')
   and upper(s.module) like nvl( upper('&module'), '%')
   and e.event like nvl('&event', '%')
   and s.inst_id = e.inst_id
   and s.sid -0 = e.sid
   and s.osuser = 'boadmtst'
   and s.username = 'BO_PAL'
  and e.event not like '%SQL*Net%'
 order by elapsed_secs desc, e.inst_id, e.sid, e.event;
rem
COLUMN   module wrap

Key Features

• RAC-Aware Analysis: Monitors session events across all instances
• Business Objects Focus: Specifically targets BO application sessions
• Comprehensive Metrics: Shows wait times, timeouts, and averages
• Flexible Filtering: Supports filtering by instance, session, module, and event
• Network Event Exclusion: Filters out SQL*Net events for database focus
• Session Duration Tracking: Calculates elapsed time since logon
• Statistical Summaries: Provides computed totals and averages

Usage

@gvsessev9_bo.sql

Input Parameters:

• inst_id: Instance ID to filter (press Enter for all)
• sid: Session ID to filter (press Enter for all)
• module: Module name pattern (press Enter for all)
• event: Event name pattern (press Enter for all)

Required Privileges

SELECT on GV$SESSION_EVENT
SELECT on GV$SESSION

Sample Output

                                            Global Session Events

                                   ELAPSED     TOTAL     TOTAL     TIME  TIME   MAX  AVERAGE
INST SESS USERNAME     SECS EVENT         WAITS  TIMEOUTS   OUT WAITED  WAIT    WAIT
              SID                                           PCT  (secs)  (ms)    (ms)
---- ---- ---------- ------- -------------------------- -------- -------- ----- ------- ------ -------
   1  145 BO_PAL      12456 db file sequential read      1,234,567        0   0.0  456.78     15    0.37
                             db file scattered read        567,890        0   0.0  234.56     25    0.41
                             CPU time                             0        0   0.0      0.00      0       0
                             direct path read                 45,678        0   0.0   12.34     8    0.27
                             log file sync                   23,456      123   0.5    8.90    45    0.38

   2  289 BO_PAL       8934 db file sequential read        987,654        0   0.0  123.45     12    0.12
                             db file scattered read        234,567        0   0.0   67.89     18    0.29
                             enq: TX - row lock content       1,234        0   0.0   15.67  2500   12.70
                             latch: cache buffers chains     12,345        0   0.0    2.34     5    0.19

sum                                                       3,107,373      123        920.03

Key Output Columns

• INST: RAC instance ID where the session is running
• SESS SID: Session ID
• USERNAME: Database username (filtered to BO_PAL)
• ELAPSED SECS: Time since session logon in seconds
• EVENT: Wait event name
• TOTAL WAITS: Total number of waits for this event
• TOTAL TIMEOUTS: Number of waits that timed out
• TIME OUT PCT: Percentage of waits that timed out
• TIME WAITED (secs): Total time waited for this event in seconds
• MAX WAIT (ms): Maximum single wait time in milliseconds
• AVERAGE WAIT (ms): Average wait time per event in milliseconds

Understanding Business Objects Performance

Common BO Performance Patterns

Report Generation Waits

-- Typical BO report generation events:
-- db file sequential read: Index lookups
-- db file scattered read: Full table scans
-- direct path read: Large data retrievals
-- temp space usage: Complex aggregations

Data Processing Events

-- BO data processing characteristics:
-- High I/O for large datasets
-- CPU-intensive calculations
-- Memory allocation for caching
-- Lock contention during updates

Universe Access Patterns

-- BO Universe query patterns:
-- Complex joins across multiple tables
-- Aggregate functions and grouping
-- Subquery processing
-- Materialized view refreshes

Performance Baselines

Acceptable Performance

-- Good BO performance indicators:
-- db file sequential read: < 5ms average
-- db file scattered read: < 30ms average
-- CPU time dominates wait time
-- Low timeout percentages

Performance Issues

-- BO performance problems:
-- db file sequential read: > 10ms average
-- db file scattered read: > 50ms average
-- High enqueue waits
-- Significant timeout percentages

Common Use Cases

1. BO Application Monitoring

   • Monitor Business Objects application performance
   • Identify slow-running reports and queries
   • Track resource utilization patterns
   • Analyze peak usage impact

2. Performance Troubleshooting

   • Diagnose BO application slowdowns
   • Identify database bottlenecks affecting BO
   • Analyze wait event patterns
   • Support performance optimization efforts

3. Capacity Planning

   • Monitor BO resource consumption trends
   • Plan infrastructure capacity
   • Assess scalability requirements
   • Support growth planning

4. RAC Load Balancing

   • Analyze BO session distribution across instances
   • Identify instance-specific performance issues
   • Plan workload balancing strategies
   • Monitor cluster resource utilization

Advanced Analysis

Session Duration Analysis

-- ELAPSED SECS interpretation:
-- Long-running sessions indicate:
-- Complex report processing
-- Large data extractions
-- Connection pooling efficiency
-- Potential session leaks

Wait Event Correlation

-- Event pattern analysis:
-- High db file reads + low CPU = I/O bound
-- High CPU + low waits = CPU bound
-- High lock waits = contention issues
-- High temp space = memory pressure

Timeout Analysis

-- Timeout interpretation:
-- Resource contention indicators
-- System overload symptoms
-- Network or storage issues
-- Application configuration problems

Troubleshooting BO Performance

I/O Performance Issues

Sequential Read Problems

-- High db file sequential read waits:
-- Index inefficiency
-- Poor SQL execution plans
-- Storage subsystem issues
-- Buffer pool sizing problems

Scattered Read Issues

-- High db file scattered read waits:
-- Excessive full table scans
-- Missing or ineffective indexes
-- Poor universe design
-- Statistics staleness

Resource Contention

Lock Contention

-- Enqueue wait events indicate:
-- Concurrent report conflicts
-- Data modification contention
-- Schema lock issues
-- Deadlock scenarios

Latch Contention

-- Latch wait events suggest:
-- Buffer pool contention
-- Library cache issues
-- Redo log contention
-- System scalability limits

Memory and CPU Issues

Memory Pressure

-- Memory-related events:
-- PGA memory allocation failures
-- Temp tablespace usage
-- Sort area size issues
-- Work area optimization needs

CPU Bottlenecks

-- CPU-related analysis:
-- High CPU time vs. wait time
-- Parse time excessive
-- Context switching overhead
-- Inefficient SQL processing

Business Objects Optimization

Universe Design

-- Universe optimization:
-- Efficient table joins
-- Proper indexing strategies
-- Aggregate awareness
-- Context optimization

Report Optimization

-- Report performance:
-- Query optimization
-- Data source efficiency
-- Caching strategies
-- Prompt optimization

Database Tuning

-- Database optimization for BO:
-- Statistics maintenance
-- Index optimization
-- Partition strategies
-- Memory configuration

Filtering and Analysis

Instance-Specific Analysis

-- Monitor specific instance:
Enter value for inst_id: 1
Enter value for sid:
Enter value for module:
Enter value for event:

Session-Specific Monitoring

-- Monitor specific session:
Enter value for inst_id:
Enter value for sid: 145
Enter value for module:
Enter value for event:

Event-Specific Analysis

-- Monitor specific events:
Enter value for inst_id:
Enter value for sid:
Enter value for module:
Enter value for event: db file%

Module-Based Filtering

-- Monitor specific BO modules:
Enter value for inst_id:
Enter value for sid:
Enter value for module: %WebI%
Enter value for event:

Integration with Monitoring

Automated Monitoring

-- Schedule regular execution:
-- Monitor BO application health
-- Alert on performance degradation
-- Track trends over time
-- Generate performance reports

Threshold Management

-- Define performance thresholds:
-- Maximum acceptable wait times
-- Timeout percentage limits
-- Session duration alerts
-- Resource utilization warnings

Historical Analysis

-- Track performance trends:
-- Compare peak vs. off-peak performance
-- Monitor growth patterns
-- Identify seasonal variations
-- Support capacity planning

RAC-Specific Considerations

Load Distribution

-- Analyze BO load across instances:
-- Session distribution patterns
-- Performance variation by instance
-- Instance affinity effectiveness
-- Cluster resource utilization

Interconnect Impact

-- Monitor cluster-specific events:
-- gc (global cache) events
-- Cross-instance coordination
-- Interconnect performance
-- Instance isolation effects

Service Management

-- BO service optimization:
-- Service-based connection routing
-- Load balancing configuration
-- Failover behavior
-- Performance service targeting

Performance Baselines and SLAs

Establishing Baselines

-- BO performance baselines:
-- Peak hour performance standards
-- Report execution time limits
-- Resource utilization norms
-- User experience metrics

SLA Monitoring

-- Service level monitoring:
-- Response time requirements
-- Availability targets
-- Throughput expectations
-- Error rate thresholds

Trend Analysis

-- Performance trending:
-- Weekly/monthly comparisons
-- Growth impact assessment
-- Seasonal pattern recognition
-- Predictive capacity planning

Related Scripts

• gvsess.sql - Comprehensive session analysis
• gvwait.sql - Global wait event analysis
• vsessio.sql - Session I/O statistics
• session-wait.sql - Current session waits

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Daily BO health checks
-- Peak hour performance monitoring
-- Weekly trend analysis
-- Monthly capacity reviews

Documentation

-- Maintain performance documentation:
-- Baseline performance metrics
-- Known performance issues
-- Optimization procedures
-- Escalation processes

Proactive Management

-- Preventive measures:
-- Regular universe optimization
-- Database maintenance schedules
-- Capacity planning activities
-- Performance testing procedures

Customization Notes

Adapting for Other Applications

-- To modify for other applications:
-- Change s.osuser filter
-- Change s.username filter
-- Adjust event exclusions
-- Modify break and compute clauses

Environment-Specific Tuning

-- Environment adaptations:
-- Adjust timeout thresholds
-- Modify formatting for local needs
-- Add application-specific events
-- Include custom calculations

Summary

This script is essential for:

1. BO Application Monitoring - Tracking Business Objects performance metrics
2. Performance Troubleshooting - Identifying and resolving BO-specific issues
3. Capacity Planning - Understanding BO resource consumption patterns
4. RAC Optimization - Analyzing BO workload distribution across instances
5. SLA Management - Monitoring service level compliance for BO applications