Oracle ASM and data rebalancing between the volumes

In this post I want to show how data is distributed evenly between all the volumes of an Oracle ASM diskgroup eg. If we have 2 x 100 GB volumes in our disk group, and 100 GB of data. Then the data is distributed evenly between each of the volumes ie. the first volume will have 50GB and the second volume will have 50GB. Now if I add a 3rd volume to the disk group, then the data will be re-balanced between the three volumes ie. each volume will have 33.33GB of the data.

The initial distribution of data between the volumes, and the later re-balancing of the data is done based on the current data capacity, and not on the I/O pattern. This means that if we do have a table that is heavily accessed, we could potentially have hot-spots and degradation in performance due to the limitations of HDD disks.

Here is the informtion I got just after creating the Oracle ASM diskgroup +DATA using hdisk21, hdisk22, hdisk23, and hdisk24 on an AIX system. Each volume in this case is 300 GB

SQL> select name, path, free_mb, total_mb from v$asm_disk; NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ---------- DATA_0000 /dev/rhdisk21 NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ----------     307180     307200 DATA_0001 /dev/rhdisk22     307185     307200 DATA_0002 NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ---------- /dev/rhdisk23     307184     307200 DATA_0003 /dev/rhdisk24     307185     307200 4 rows selected. SQL>

After creating the database, schema, tables, and filling it with data, here is what I have. As you see the data is evenly distributed between all the volumes of the Oracle ASM diskgroup.

SQL> select name,path, free_mb, total_mb from v$asm_disk; NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ---------- DATA_0000 /dev/rhdisk21 NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ----------     161103     307200 DATA_0001 /dev/rhdisk22     161111     307200 DATA_0002 NAME ------------------------------ PATH --------------------------------------------------------------------------------    FREE_MB   TOTAL_MB ---------- ---------- /dev/rhdisk23     161113     307200 DATA_0003 /dev/rhdisk24     161108     307200 4 rows selected. SQL>