@@header

COLUMN diskgroup_name      HEADING "DiskGroup Name"   FORMAT a20
COLUMN Imbalance_percent   HEADING "Imbalance|(%)"    FORMAT 99999999.99
COLUMN Variance            HEADING "Variance"         FORMAT 999.99999
COLUMN MinFree             HEADING "MinFree|(%)"      FORMAT 999.99
COLUMN DiskCnt             HEADING "Disk Count"       FORMAT 99999
COLUMN Type             HEADING "Redundancy|Typet"    FORMAT a10

select g.name diskgroup_name
    ,  100*(max((d.total_mb-d.free_mb + (128*g.allocation_unit_size/1048576))/(d.total_mb + (128*g.allocation_unit_size/1048576)))
           -min((d.total_mb-d.free_mb + (128*g.allocation_unit_size/1048576))/(d.total_mb + (128*g.allocation_unit_size/1048576)))
          ) /max((d.total_mb-d.free_mb + (128*g.allocation_unit_size/1048576))/(d.total_mb + (128*g.allocation_unit_size/1048576))) 
         Imbalance_percent
    , 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb)  Variance /* Percent difference between largest and smallest disk */
    , 100*(min(d.free_mb/d.total_mb)) MinFree /* The disk with the least free space as a percent of total space */
    , count(*) DiskCnt                        /* Number of disks in the diskgroup */
    , g.type Type                             /* Diskgroup redundancy */
FROM v$asm_disk_stat d 
   , v$asm_diskgroup_stat g
WHERE d.group_number = g.group_number 
AND d.group_number <> 0 
AND d.state = 'NORMAL' 
AND d.mount_status = 'CACHED'
GROUP BY g.name 
       , g.type
ORDER BY g.name       
;

@@footer