Skip to content
Oracle DBA Scripts & Database Utilities

Health Check Suite 2 (health2.sql)

What This Script Does

Section titled “What This Script Does”

Specialized health check script that focuses on concurrency analysis through blocking lock detection and Oracle Streams capture monitoring. This targeted approach provides deep insight into specific database performance areas.

The Script

Section titled “The Script”
rem  health2.sql
rem
ttitle 'Database Health Check Suite 2'
rem
set pause off
set pagesize 200
set time on
rem
set term off
rem
ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-YYYY HH24:MI'
ALTER SESSION SET NLS_timestamp_FORMAT = 'MON-DD-YYYY HH24:MI'
rem
column v_logname print new_value v_logname format a50
rem
select 'Check2_' || name || '_' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),6,3)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),10,2)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),1,4)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),13,2) || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),16,2) ||
       '.txt'  v_logname
 from v$database
/
rem
set term on
rem
spool &v_logname
rem
rem -- Health check components:
rem -- @gvlockb
rem -- @gvscapture
rem
spool off

Usage

Section titled “Usage”
-- Run specialized health check
@health2.sql

Parameters

Section titled “Parameters”

The script requires no input parameters and automatically generates output files.

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT on GV$LOCK
SELECT on GV$SESSION
SELECT on GV$STREAMS_CAPTURE

Output File Format

Section titled “Output File Format”

The script generates output files with this naming pattern:

Check2_[DATABASE_NAME]_[MON-DD-YYYY-HH-MM].txt

Example: Check2_PROD_JUN-01-2025-14-30.txt

Health Check Components

Section titled “Health Check Components”

This script typically calls:

Concurrency Analysis

  • gvlockb - Blocking lock analysis across RAC instances
  • gvscapture - Oracle Streams capture process monitoring

Focus Areas

Section titled “Focus Areas”

Lock Contention Detection

  • Identifies blocking and blocked sessions
  • Analyzes lock types and duration
  • RAC-wide lock analysis

Streams Performance

  • Capture process efficiency
  • Apply process performance
  • Replication lag analysis

Common Use Cases

Section titled “Common Use Cases”

Concurrency Issue Investigation

-- During reported locking issues
@health2.sql
-- Identify blocking sessions and lock types

Streams Monitoring

-- Regular Streams health assessment
@health2.sql
-- Monitor capture and apply performance

Application Performance Analysis

-- Investigate application slowdowns
@health2.sql
-- Check for lock-related bottlenecks

RAC Contention Analysis

-- Cross-instance lock analysis
@health2.sql
-- Identify inter-node contention

Key Metrics Monitored

Section titled “Key Metrics Monitored”

Lock Analysis

  • Blocking session identification
  • Lock wait times and types
  • Resource contention patterns

Streams Monitoring

  • Capture lag measurements
  • Apply process efficiency
  • Queue depth analysis

Benefits

Section titled “Benefits”

Targeted Analysis

  • Focuses on specific performance areas
  • Detailed concurrency investigation
  • Streams-specific diagnostics

RAC Awareness

  • Cross-instance lock analysis
  • Global resource contention
  • Cluster-wide performance view

Automated Collection

  • Consistent execution framework
  • Timestamped output files
  • Repeatable diagnostic process

Performance Impact

Section titled “Performance Impact”
  • Low to Moderate: Depends on system lock activity
  • Targeted Queries: Focuses on specific system views
  • RAC Efficient: Optimized for cluster environments

Output Analysis

Section titled “Output Analysis”

Lock Analysis Results

  • Review blocking session patterns
  • Identify frequently contended resources
  • Analyze lock duration and impact

Streams Results

  • Monitor capture and apply lag
  • Check for processing bottlenecks
  • Verify replication efficiency

Troubleshooting Tips

Section titled “Troubleshooting Tips”

High Lock Contention

  • Identify application design issues
  • Review transaction scope and duration
  • Consider indexing improvements

Streams Performance Issues

  • Check network connectivity
  • Verify apply process configuration
  • Monitor queue management
Section titled “Related Scripts”