Identify files with imbalanced allocation in an ASM diskgroup

Below SQL script to identify files with imbalanced extents across the disks in an ASM diskgroup.

Set ORACLE_SID is point to ASM instance.


select number_kffxp file#, max(count1) max,min(count1) min from
(select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and disk_kffxp != 65534
group by number_kffxp,disk_kffxp
order by number_kffxp,disk_kffxp)
group by number_kffxp;


 FILE#        MAX        MIN
---------- ---------- ----------
      3242      2523       3414
      3243      3893       6824
      3244      7853      10581
      3924     10583     10581
      4098      6828       6827


 Max and min columns should be close to each other for balanced files



From the output above, files with imbalanced extents are file# 3242, 3243 and 3244.

File# 3924 and 4098 have extents almost equally balanced. Small imbalances are expected.

Number of extents per disk can be drilled down for certain files by a query like:

select number_kffxp ,disk_kffxp,count(XNUM_KFFXP) count1
from x$kffxp
where group_kffxp=<group_number>
and number_kffxp=<number_kffxp>