-- 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;
Posts
