Skip to content
Oracle DBA Scripts & Database Utilities

ASM Disk Performance Metrics (vasm_disk.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive ASM disk performance metrics including:

  • I/O statistics (reads and writes)
  • Service times for read and write operations
  • Average bytes per I/O operation
  • Disk space usage (total and free MB)
  • Grouping by disk group for easy analysis

Script

Section titled “Script”
set lines 132
col name format a15
col group_number format 999 heading 'GRP|NBR'
col path format a25
col reads format 999,999,999
col writes format 9999999
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'
col total_mb format 999,999,999 head 'TOTAL MB'
col free_mb format 9,999,999 head 'FREE MB'
break on report skip 1 on group_number skip 1
compute sum of total_mb free_mb on report group_number
select
       group_number,
       name,
       path,
       -- create_date,
       -- mount_date,
       reads,
       -- read_time,
       read_time/decode(reads, 0, 1, reads)*1000 rd_avg,
       bytes_read/decode(reads,0,1, reads) byte_read,
       writes,
       write_time/decode(writes, 0, 1, writes)*1000 wr_avg,
       bytes_written/decode(writes,0,1, writes) byte_write
       , total_mb,
       free_mb
  from v$asm_disk
 order by
       group_number,
       name,
       path
/

Usage

Section titled “Usage”
SQL> @vasm_disk.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$ASM_DISK
  • Must be run from an ASM instance or database instance with ASM access

Sample Output

Section titled “Sample Output”
GRP                                                          READ    Bytes         WRITE    Bytes
NBR NAME            PATH                            READS SVC TIME      per    WRITES SVC TIME      per      TOTAL MB    FREE MB
--- --------------- ------------------------- ------------ -------- --------- --------- -------- --------- ------------ ----------
  1 DATA_0000       /dev/raw/raw1                  123,456     5.23    32,768   456,789     3.45    32,768      512,000    123,456
    DATA_0001       /dev/raw/raw2                  234,567     4.89    32,768   567,890     3.12    32,768      512,000    123,456
*** sum                                                                                                       1,024,000    246,912


  2 FRA_0000        /dev/raw/raw3                   45,678     6.78    32,768    67,890     4.56    32,768      256,000    189,000
    FRA_0001        /dev/raw/raw4                   56,789     7.12    32,768    78,901     4.89    32,768      256,000    189,000
*** sum                                                                                                         512,000    378,000

Key Output Columns

Section titled “Key Output Columns”
  • GRP NBR: ASM disk group number
  • NAME: ASM disk name within the group
  • PATH: Physical path to the disk device
  • READS: Total number of read operations
  • READ SVC TIME (ms): Average read service time in milliseconds
  • Bytes per Read: Average bytes per read operation
  • WRITES: Total number of write operations
  • WRITE SVC TIME (ms): Average write service time in milliseconds
  • Bytes per Write: Average bytes per write operation
  • TOTAL MB: Total disk capacity in megabytes
  • FREE MB: Available free space in megabytes

Understanding the Metrics

Section titled “Understanding the Metrics”

Service Times

Section titled “Service Times”
  • Good: < 10ms for both reads and writes
  • Warning: 10-20ms may indicate I/O subsystem stress
  • Critical: > 20ms suggests serious I/O performance issues

I/O Patterns

Section titled “I/O Patterns”
  • Compare bytes per read/write to identify I/O sizes
  • 8KB typical for OLTP, larger for data warehouse
  • Uneven I/O distribution may indicate hot spots

Space Usage

Section titled “Space Usage”
  • Monitor free space across disk groups
  • Ensure balanced space usage within groups
  • Plan for growth before hitting capacity limits

Common Use Cases

Section titled “Common Use Cases”

  1. Performance Troubleshooting

    • Identify slow disks with high service times
    • Find I/O hot spots across disk groups
    • Validate storage performance after changes

  2. Capacity Planning

    • Monitor space usage trends
    • Plan disk group expansion
    • Balance data across disk groups

  3. Storage Validation

    • Verify new storage meets performance requirements
    • Compare performance across different storage tiers
    • Validate redundancy and striping effectiveness
Section titled “Related Scripts”