30 lines
1.5 KiB
SQL
30 lines
1.5 KiB
SQL
@@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 |