“Holy Imbalance, DBAtman!”
Is that right?
It can be useful if you work frequently with OEM metrics…
# OEM’s 12c Query
SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count FROM x$kffxp WHERE group_kffxp = 1 AND disk_kffxp != 65534 GROUP BY number_kffxp, disk_kffxp ORDER BY number_kffxp, disk_kffxp) GROUP BY file_num HAVING MAX(extent_count) - MIN(extent_count) > 5 ORDER BY disk_extents_imbalance DESC;
# DBAtman’s Query
select max(free_mb) biggest, min(free_mb) lowest, avg(free_mb) AVG, trunc(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb))),2)||'%' as balanced, trunc(100-(GREATEST ((avg(free_mb)*100/max(free_mb)),(min(free_mb)*100/avg(free_mb)))),2)||'%' as inbalanced from v$asm_disk where group_number in (select group_number from v$asm_diskgroup where name = upper('&DG'));
I made my own query for two reasons:
1) I didn’t have the OEM query in the time i made it.
2) My query measures the imbalance with the avg of the disks (if every disk would balanced, how would be the difference), rather than the real/present difference between the disk with the maximum and the minimum usage…
So, you can chose the one you need… 🙂
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!