Translate

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
 JOIN
 (SELECT group_number, COUNT(DISTINCT disk_number)
active_disks,
 SUM(reads) reads, SUM(writes) writes,
 SUM(read_time) read_time, SUM(write_time)
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)
read_secs,
 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 dg.name = '&diskgroup_name'
 AND d.mount_status = 'CACHED'
 ORDER BY d.PATH

--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,d.name diskgroup_name,f.TYPE, a.name 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,
 parent_index
 FROM v$asm_alias a
 CONNECT BY PRIOR reference_index = parent_index) a
 JOIN (SELECT DISTINCT name
 FROM v$asm_alias
 WHERE parent_index = group_number * POWER(2, 24)) b
 ON (a.rootname = b.name)
 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)
 WHERE a.CONNECT_BY_ISLEAF = 1
 ORDER BY (cold_reads+cold_writes+hot_reads+hot_writes) DESC;

 SELECT t.name 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)
 WHERE d.name = 'DATA'
 ORDER BY t.name;

Follow by Email