Understanding Usable_MB in Oracle ASM

USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure

External Redundancy : Total_MB 1000 mb USABLE MB 1000 MB

NORMAL REDUNDANCY : TOTAL_MB :1000 MB USABLE_MB : (free_mb – required_mirror_free_mb) / 2

HIGH REDUNDANCY : TOTAL MB :1000 MB USABLE_MB: (free_mb – required_mirror_free_mb) / 3

Looks simple but there are factors which influence the required_mirror_free_mb

Let see the below setup now

The Test Diskgroup has two failure groups (FG1 and FG2) each contains 3 disk with capacity of 5GB each

USABLE_MB=(free_mb – required_mirror_free_mb) / 2

Now the Required_mirror_free_mb=5119MB

Thats the size of a single asm disk( Disk with max size is considered), In Normal redundancy we can tolerate single disk failure

USABLE_MB=(30600-5119)/2 =12740

Now lets see a different setup :

Lets create a Diskgroup with 3 failgroups (FG1,FG2,F3) with 2 disks each 5 GB

Now the surprise here is the Required_Mirror_MB has been changed from 5119 to 10238,

The reason being that even we loose a the third failure group FG3 we still can maintain a normal redundancy with 2 existing failure groups, Hence the required_mirror_free_mb will change from a single disk to a single failure group

Note that 3 FG in normal redundancy is different from 3FG with high redundancy

You can see the REQUIRED_MIRROR_FREE_MB is same for the both config(NORMAL 3FG and HIGH 3FG) but the USABLE_FILE_MB=6770

USABLE_MB: (free_mb – required_mirror_free_mb) / 3

USABLE_MB:(30549-10238)/3=6770

Now lets try High redundancy with 4 FG

Now the Required_mirror_mb has been increased from 10238 to 15357
The current setup can tolerate FG4 failure and can still operate in High Redundancy with default 2 disk failure , so that makes 3 disks total in this setup

5119*3=15357

USABLE_MB: (30549 – 15357) / 3=5064 MB

I/O load on ASM DISKGROUP

Below is one of the method how you start troubleshooting I/O load balancing in ASM.

The Below query gives how good the data is balanced in any given diskgroup  (o/p should be near to 100%)

Please note that below example is from external redundancy , So there are no failure group available

SELECT dg.group_number “GROUP#”,

dg.name,

DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 – df.sum_io / total_dg.total_io), -1, 0, (1 – df.sum_io / total_dg.total_io)))) “IO_BALANCED”

FROM (SELECT d.group_number group_number,

SUM (ABS ((d.reads + d.writes) – tot.avg_io)) sum_io

FROM v$asm_disk_stat d,

(SELECT group_number,

SUM (reads) + SUM (writes),

DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io

FROM v$asm_disk_stat

WHERE header_status = ‘MEMBER’

GROUP BY group_number) tot

WHERE header_status = ‘MEMBER’ AND tot.group_number = d.group_number

GROUP BY d.group_number) df,

(SELECT group_number,

SUM (reads) + SUM (writes) total_io

FROM v$asm_disk_stat

WHERE header_status = ‘MEMBER’

GROUP BY group_number) total_dg,

V$ASM_DISKGROUP dg

WHERE df.group_number = total_dg.group_number

AND df.group_number = dg.group_number

The Output should be close to 100  , If its less than 80 check if all the disk on the diskgroup are of same size

CASE STUDY :

GROUP# NAME                           IO_BALANCED

———- —————————— ———–

1 DATA                            60.7173153

2 FLASH                           46.7913472

Below table provides you the size of each disk and the read and write on each disk

You can see that the ASM_009,010,011,012 are 200 GB instead of 99 or 100GB ,

This makes the data balancing tough in the Diskgroup

ASM_006,007,008 has more read compared to the other disk.

Group	Disk	Header	Mode	State	Added to Diskgroup	Total_GB	Free_GB	Disk_Name	Path	PerRead	PerWrite
1	0	MEMBER	ONLINE	NORMAL	5-Apr-15	99.996094	0.234375	ASM_001	ORCL:ASM_001	0.008	0.001
1	1	MEMBER	ONLINE	NORMAL	6-May-15	99.996094	0.226563	ASM_002	ORCL:ASM_002	0.008	0.002
1	2	MEMBER	ONLINE	NORMAL	25-May-15	99.996094	0.222656	ASM_003	ORCL:ASM_003	0.007	0.002
1	3	MEMBER	ONLINE	NORMAL	28-May-15	100	0.222656	ASM_006	ORCL:ASM_006	0.013	0.002
1	4	MEMBER	ONLINE	NORMAL	28-May-15	100	0.222656	ASM_007	ORCL:ASM_007	0.012	0.004
1	5	MEMBER	ONLINE	NORMAL	28-May-15	99	0	ASM_008	ORCL:ASM_008	0.012	0.003
1	6	MEMBER	ONLINE	NORMAL	30-May-15	199.99609	0.476563	ASM_009	ORCL:ASM_009	0.006	0.002
1	7	MEMBER	ONLINE	NORMAL	30-May-15	199.99609	0.484375	ASM_010	ORCL:ASM_010	0.006	0.002
1	8	MEMBER	ONLINE	NORMAL	30-May-15	199.99609	0.488281	ASM_011	ORCL:ASM_011	0.007	0.002
1	9	MEMBER	ONLINE	NORMAL	6-Jul-16	200.00391	0.507813	ASM_012	ORCL:ASM_012	0.007	0.002
2	0	MEMBER	ONLINE	NORMAL	5-Apr-15	99.996094	48.89453	ASM_005	ORCL:ASM_005	0.002	0.002
2	1	MEMBER	ONLINE	NORMAL	25-May-15	99.996094	48.91016	ASM_004	ORCL:ASM_004	0.002	0.003