Search This Blog

ASM I/O queries

-- ASM I/O load and I/O response QUERIES

Below query returns name of disk group, average read/write time in millisecs, total number of reads/writes

Query 1:

SELECT name, ROUND(total_mb / 1024) total_gb, active_disks,
 reads / 1000 reads1k, writes / 1000 writes1k,
 ROUND(read_time) read_time, ROUND(write_time) write_time,
 ROUND(read_time * 1000 / reads, 2) avg_read_ms,
 ROUND(write_time * 1000 / writes, 2) avg_write_ms
 FROM v$asm_diskgroup_stat dg
 (SELECT group_number, COUNT(DISTINCT disk_number)
 SUM(reads) reads, SUM(writes) writes,
 SUM(read_time) read_time, SUM(write_time)
 FROM gv$asm_disk_stat
 WHERE mount_status = 'CACHED'
 GROUP BY group_number) ds
 ON (ds.group_number = dg.group_number)
 ORDER BY dg.group_number

If you want above query returns load on ASM instance disk groups instance wise then you can replace view gv$asm_disk_stat by v$asm_disk_stat in above query

Below  query returns asm disk name, average read and total number of reads/write  
Query 2: query takes name of disk group as parameter
 SELECT d.PATH disk_path, d.total_mb,
 ROUND(ds.read_secs * 1000 / ds.reads, 2) avg_read_ms,
 ds.reads/1000 + ds.writes/1000 io_1k,
 ds.read_secs +ds.write_secs io_secs,
 ROUND((d.reads + d.writes) * 100 /
 SUM(d.reads + d.writes) OVER (),2) pct_io,
 ROUND((ds.read_secs +ds.write_secs)*100/
 SUM(ds.read_secs +ds.write_secs) OVER (),2) pct_time
 FROM v$asm_diskgroup_stat dg
 JOIN v$asm_disk_stat d ON (d.group_number = dg.group_number)
 JOIN (SELECT group_number, disk_number disk_number,
SUM(reads) reads,
 SUM(writes) writes, ROUND(SUM(read_time), 2)
 ROUND(SUM(write_time), 2) write_secs
 FROM gv$asm_disk_stat
 WHERE mount_status = 'CACHED'
 GROUP BY group_number, disk_number) ds
 ON (ds.group_number = d.group_number
 AND ds.disk_number = d.disk_number)
 WHERE = '&diskgroup_name'
 AND d.mount_status = 'CACHED'

--rebalance operations

Query 3:

SELECT dg.NAME, d.operation, d.state, d.POWER, d.actual,
 est_work ,
 d.sofar*100/d.est_work pct_done, d.est_rate, d.est_minutes
 FROM v$asm_diskgroup dg LEFT OUTER JOIN gv$asm_operation d
 ON (d.group_number = dg.group_number);

Below query returns file names and assocoated template and stripping type

  Query 4:

 SELECT rootname, diskgroup_name,f.TYPE, filename,
 space / 1048576 allocated_mb, primary_region, striped,
 round((hot_reads + hot_writes)/1000,2) hot_ios1k,
 round((cold_reads + cold_writes)/1000,2) cold_ios1k
 FROM (SELECT CONNECT_BY_ISLEAF, group_number, file_number, name,
 CONNECT_BY_ROOT name rootname, reference_index,
 FROM v$asm_alias a
 CONNECT BY PRIOR reference_index = parent_index) a
 FROM v$asm_alias
 WHERE parent_index = group_number * POWER(2, 24)) b
 ON (a.rootname =
 JOIN v$asm_file f
 ON (a.group_number = f.group_number
 AND a.file_number = f.file_number)
 JOIN v$asm_diskgroup d
 ON (f.group_number = d.group_number)
 ORDER BY (cold_reads+cold_writes+hot_reads+hot_writes) DESC;

 SELECT template_name, t.SYSTEM, t.redundancy,
 t.stripe, t.primary_region
 FROM v$asm_template t
 JOIN v$asm_diskgroup d
 ON (d.group_number = t.group_number)

Follow by Email