ASM Disk Group Summary (vasmdsumsz.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 Oracle ASM (Automatic Storage Management) script provides a comprehensive summary of disk group performance by analyzing I/O statistics, service times, and space utilization. It aggregates data from v$asm_disk to show performance metrics grouped by disk path prefix.

The Script

set lines 132
col name format a15
col path format a20
col reads format 999999999 head 'READS'
col writes format 999999999 head 'WRITES'
col rd_avg forma 9999.00 head 'READ|SVC|TIME|(ms)'
col wr_avg forma 9999.00 head 'WRITE|SVC|TIME|(ms)'
col byte_Read format 999999999 head 'Bytes|per|Read'
col byte_write format 999999999 head 'Bytes|per|Write'
select
   substr(path, 1, instr(path, '.')-1) path,
   sum(reads) reads,
   sum(read_time)/decode(sum(reads), 0, 1, sum(reads))*100 rd_avg,
   sum(bytes_read)/sum(reads) byte_read,
   sum(writes) writes,
   sum(write_time)/decode(sum(writes), 0, 1, sum(writes))*100 wr_avg,
   sum(bytes_written)/sum(writes) byte_write,
   sum(TOTAL_MB) total_mb,
   sum(FREE_MB) free_mb,
   count(*)
from v$asm_disk
group by
   substr(path, 1, instr(path, '.')-1)
order by 1
/

Usage

-- Run the script to analyze all ASM disk groups
@vasmdsumsz.sql

No parameters required - automatically analyzes all ASM disk groups.

Required Privileges

• SELECT privilege on v$asm_disk
• ASM instance must be running
• Connection to ASM instance or database with ASM access

Sample Output

PATH                     READS      READ     Bytes      WRITES     WRITE    Bytes    TOTAL_MB   FREE_MB COUNT(*)
                                    SVC      per                   SVC      per
                                    TIME     Read                  TIME     Write
                                    (ms)                           (ms)
-------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------
/dev/asm/data1        15234567     12.45   1048576   8765432     15.23   1048576    512000    256000       4
/dev/asm/fast1         9876543      8.12   1048576   4567890      9.87   1048576    256000    128000       2
/dev/asm/logs1         2345678     11.34    524288   5432109     13.45    524288    128000     64000       2

Key Output Columns

• PATH: Disk group path prefix (derived from full disk path)
• READS: Total number of read operations across all disks in group
• READ SVC TIME (ms): Average read service time in milliseconds
• Bytes per Read: Average bytes transferred per read operation
• WRITES: Total number of write operations across all disks in group
• WRITE SVC TIME (ms): Average write service time in milliseconds
• Bytes per Write: Average bytes transferred per write operation
• TOTAL_MB: Total space in megabytes across all disks in group
• FREE_MB: Free space in megabytes across all disks in group
• COUNT(*): Number of disks in the group

Understanding the Results

Performance Metrics

1. Service Time Analysis

   • Good: Read/write service times under 10ms
   • Acceptable: Service times 10-20ms
   • Poor: Service times over 20ms consistently
   • High service times indicate storage performance issues

2. I/O Patterns

   • Bytes per Read/Write: Shows typical I/O size
   • 1MB I/O: Indicates large sequential operations
   • Smaller I/O: May indicate random access patterns
   • Compare patterns across different disk groups

3. Space Utilization

   • Free Space Percentage: (FREE_MB / TOTAL_MB) * 100
   • Monitor trends: Decreasing free space over time
   • Capacity planning: Plan expansion before running low

Disk Group Types

1. Data Disk Groups

   • Typically show mixed read/write patterns
   • Larger I/O sizes for table scans
   • Monitor for balanced performance

2. Fast Recovery Area (FRA)

   • High write activity during backups
   • Large sequential I/O patterns
   • Monitor space usage carefully

3. Redo Log Groups

   • High write activity, sequential patterns
   • Small I/O sizes typically
   • Critical for performance - monitor service times

Common Use Cases

1. Performance Troubleshooting

-- Identify disk groups with high service times
@vasmdsumsz.sql
-- Look for service times > 20ms

2. Capacity Planning

-- Monitor space utilization trends
@vasmdsumsz.sql
-- Calculate free space percentages

3. I/O Analysis

-- Analyze I/O patterns across disk groups
@vasmdsumsz.sql
-- Compare read vs write activity

4. Storage Performance Assessment

-- Evaluate overall ASM performance
@vasmdsumsz.sql
-- Compare against baseline measurements

Performance Analysis

Service Time Optimization

1. High Read Service Times

   • Check underlying storage performance
   • Analyze disk queue depths
   • Consider storage tiering optimization
   • Monitor for hot spots

2. High Write Service Times

   • Check redo log disk group performance
   • Analyze backup impact on storage
   • Monitor disk group balancing operations

3. Unbalanced Performance

   • Compare service times across disk groups
   • Look for storage configuration issues
   • Check for competing workloads

Capacity Management

1. Space Monitoring

   -- Calculate space utilization percentages
   SELECT path,
          total_mb,
          free_mb,
          ROUND((total_mb - free_mb) / total_mb * 100, 2) as used_pct
   FROM (your_query_results);

2. Growth Trending

   • Run script regularly to track space consumption
   • Compare results over time
   • Plan disk additions before reaching capacity limits

Troubleshooting

High Service Times

1. Storage Level Issues

   • Check storage array performance
   • Monitor disk utilization at OS level
   • Verify storage configuration optimal

2. ASM Configuration

   • Check disk group redundancy settings
   • Verify proper disk balancing
   • Monitor rebalance operations impact

Space Issues

1. Low Free Space

   • Add disks to disk groups
   • Archive or purge old data
   • Consider data compression

2. Uneven Distribution

   • Check for disk group imbalances
   • Monitor ongoing rebalance operations
   • Verify all disks are online and available

Performance Bottlenecks

1. I/O Patterns

   • Analyze application I/O requirements
   • Consider separating different workload types
   • Optimize application data access patterns

2. Hardware Limitations

   • Monitor underlying storage performance
   • Check network connectivity for storage
   • Verify storage array cache settings

Related Scripts

• ASM Disk Performance (gvasmdsk.sql) - Individual disk performance
• ASM Disk Information (vasmdsk.sql) - Detailed disk status
• ASM Disk Group Info (vasmdgrp.sql) - Disk group configuration
• Database File I/O (vfile.sql) - Database file I/O statistics

Best Practices

Monitoring Strategy

1. Regular Baseline

   • Establish performance baselines
   • Run during different time periods
   • Document normal operating ranges

2. Alerting Thresholds

   • Set alerts for service times > 20ms
   • Monitor free space below 20%
   • Alert on significant performance changes

Optimization Guidelines

1. Performance Tuning

   • Separate different workload types
   • Use appropriate redundancy levels
   • Balance disk groups properly

2. Capacity Management

   • Plan disk additions before 80% utilization
   • Monitor growth trends
   • Consider automatic storage management features

Notes

• Groups results by disk path prefix for logical organization
• Calculates averages across all disks in each group
• Service times are converted to milliseconds for readability
• Uses DECODE to avoid division by zero errors
• Shows both absolute values and derived metrics
• Useful for both performance monitoring and capacity planning