Health Check Suite (health.sql)

What This Script Does

Executes a comprehensive suite of database health check scripts and spools the output to a timestamped file. This master script coordinates multiple diagnostic checks to provide a complete health assessment of the Oracle database.

The Script

rem  health.sql
rem
ttitle 'Database Health Check Suite'
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 'Check_' || 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 would be called here:
rem -- @random_io
rem -- @scattered_io
rem -- @cpu_health
rem -- @interconnect_health
rem -- @enqueue
rem -- @session_count
rem
spool off

Usage

-- Run complete health check suite
@health.sql

Parameters

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

Required Privileges

SELECT ANY DICTIONARY
-- OR appropriate privileges for all component scripts

Output File Format

The script generates output files with this naming pattern:

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

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

Health Check Components

This script serves as a framework that typically calls:

I/O Performance Checks

random_io - Random I/O performance analysis
scattered_io - Scattered read performance analysis

Resource Utilization

cpu_health - CPU usage and efficiency metrics
interconnect_health - RAC interconnect performance (if applicable)

Concurrency Analysis

enqueue - Enqueue wait analysis
session_count - Active session counting and analysis

Common Use Cases

Regular Health Monitoring

-- Schedule daily health checks
@health.sql
-- Review generated reports for trends

Performance Troubleshooting

-- Run during performance issues
@health.sql
-- Compare with baseline health reports

Pre-Maintenance Checks

-- Establish baseline before maintenance
@health.sql
-- Document system state for comparison

Capacity Planning

-- Collect regular health metrics
@health.sql
-- Analyze trends for capacity planning

Customization

Add Additional Checks

-- Include additional diagnostic scripts:
-- @tablespace_usage
-- @memory_analysis
-- @wait_events

Modify Output Location

-- Change output directory in the script:
select 'C:\HealthChecks\Check_' || name || '_' || ...

Adjust Session Settings

-- Customize display formats:
set linesize 200
set pagesize 50

Benefits

Automated Collection

Single command runs multiple checks
Consistent output formatting
Timestamped results for tracking

Comprehensive Coverage

Multiple performance dimensions
System resource utilization
Concurrency and contention analysis

Historical Tracking

Timestamped output files
Trend analysis capabilities
Baseline comparison support

Performance Impact

Variable Impact: Depends on component scripts
Scheduled Execution: Best run during low-activity periods
Resource Usage: May consume significant I/O and CPU during execution

Output Analysis

Review Patterns

Check for consistent performance metrics
Identify any error messages or warnings
Compare with previous health check results
Look for resource utilization trends

Key Metrics to Monitor

I/O response times and throughput
CPU utilization and wait times
Session counts and activity levels
Memory usage patterns

health2 - Alternative health check suite
health3 - Extended health check
health4 - Specialized health check
Individual component scripts for focused analysis