FAQ
Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
This is despite the documentation stating :-

If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
from v$asm_disk d, v$asm_diskgroup dg
where d.group_number = dg.group_number
group by dg.name, dg.allocation_unit_size/1024/1024
/

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
FRA                                     1      11364      11470  11390
DATA                                    1       8282      14258   9170

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
FRA                                     1      72659      72920  72837
DATA                                    1      19377      30987  22157
DATAMRDW                                8      10464      15536  11654


NAME                                  MIN        MAX    AVG
------------------------------ ---------- ---------- ------
DATA                                47056      50344  48131 -- before rebalance
FRA                                 22740      22769  22752

alter diskgroup data rebalance power 8;

NAME                               AU(Mb)        MIN        MAX    AVG
------------------------------ ---------- ---------- ---------- ------
FRA                                     1      22740      22769  22752
DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

Rebalancing makes no difference. As can be seen from the last 2 examples.

To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

John

www.jhdba.wordpress.com

______________________________________________________________________
Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
and it is the responsibility of recipients to carry out their own virus checks.
______________________________________________________________________

Search Discussions

  • Herring Dave - dherri at Nov 15, 2011 at 2:31 pm
    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Nov 17, 2011 at 4:23 am
    John- this was a nice query. I am trying to answer the question- do we need to run a manual rebalance? We have not added any disks, but my problem system has a huge amount of I/O and lots of file extents. Running your query, here are some results (just the top few)-

    FILE_NUM MAX_DISK_EXTENTS MIN_DISK_EXTENTS DISK_EXTENTS_IMBALANCE
    ---------- ---------------- ---------------- ----------------------
    271 147269 6136 141133
    272 63761 2657 61104
    297 25378 1057 24321
    298 20286 845 19441
    292 8656 361 8295
    277 8277 345 7932
    258 8025 334 7691
    263 8026 335 7691
    278 7838 327 7511
    274 7525 313 7212
    276 7525 313 7212

    The underlying disk group would be made up of perhaps 6 to 8 disks of about 1 tb each, one a SAN which is striped all over the place.



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Herring Dave - dherri
    Sent: Tuesday, November 15, 2011 9:30 AM
    To: John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Nov 17, 2011 at 4:25 am
    Sorry, should have been "Dave" not John.

    -----Original Message-----
    From: CRISLER, JON A
    Sent: Wednesday, November 16, 2011 11:22 PM
    To: 'Dave.Herring@acxiom.com'; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John- this was a nice query. I am trying to answer the question- do we need to run a manual rebalance? We have not added any disks, but my problem system has a huge amount of I/O and lots of file extents. Running your query, here are some results (just the top few)-

    FILE_NUM MAX_DISK_EXTENTS MIN_DISK_EXTENTS DISK_EXTENTS_IMBALANCE
    ---------- ---------------- ---------------- ----------------------
    271 147269 6136 141133
    272 63761 2657 61104
    297 25378 1057 24321
    298 20286 845 19441
    292 8656 361 8295
    277 8277 345 7932
    258 8025 334 7691
    263 8026 335 7691
    278 7838 327 7511
    274 7525 313 7212
    276 7525 313 7212

    The underlying disk group would be made up of perhaps 6 to 8 disks of about 1 tb each, one a SAN which is striped all over the place.



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Herring Dave - dherri
    Sent: Tuesday, November 15, 2011 9:30 AM
    To: John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Herring Dave - dherri at Nov 17, 2011 at 3:58 pm
    Jon,

    I'd like to claim all sorts of research behind that query but I'm sure it's based on key MOS articles: 818171.1 (Identifying Files with Imbalances), 351117.1 (Troubleshooting ASM Space Issues), 367445.1 (Advanced Balance and Space Report on ASM).

    Should you be performing manual rebalances? I would. Unfortunately I've never been given permission to schedule regular manual rebalances due to paranoia about our current release (10.2.0.2). We see the same increasing imbalance happening on production and since ASM never performs a rebalance unless you tell it to, it eventually should be addressed. We have a lot of growth so our rebalances are happening with regular disk adds.

    If you're curious enough, review results in ASM tracefiles for ARB* background processes. ARB tracefiles list every grouping of AUs moved by timestamp, per file, so a bit of parsing and loading as an external table would help you analyze how much is being moved per file per ARB process at what times. Higher power values increase the number of ARB processes that run concurrently on each file. I've tracked this trying to resolve why some systems have much greater differences in estimated work than actual work (some close to 200% more). Unfortunately my job demands keep getting in the way of doing fun research and actually blogging my results to share.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Wednesday, November 16, 2011 10:23 PM
    To: CRISLER, JON A; Herring Dave - dherri; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    Sorry, should have been "Dave" not John.

    -----Original Message-----
    From: CRISLER, JON A
    Sent: Wednesday, November 16, 2011 11:22 PM
    To: 'Dave.Herring@acxiom.com'; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John- this was a nice query. I am trying to answer the question- do we need to run a manual rebalance? We have not added any disks, but my problem system has a huge amount of I/O and lots of file extents. Running your query, here are some results (just the top few)-

    FILE_NUM MAX_DISK_EXTENTS MIN_DISK_EXTENTS DISK_EXTENTS_IMBALANCE
    ---------- ---------------- ---------------- ----------------------
    271 147269 6136 141133
    272 63761 2657 61104
    297 25378 1057 24321
    298 20286 845 19441
    292 8656 361 8295
    277 8277 345 7932
    258 8025 334 7691
    263 8026 335 7691
    278 7838 327 7511
    274 7525 313 7212
    276 7525 313 7212

    The underlying disk group would be made up of perhaps 6 to 8 disks of about 1 tb each, one a SAN which is striped all over the place.



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Herring Dave - dherri
    Sent: Tuesday, November 15, 2011 9:30 AM
    To: John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • CRISLER, JON A at Nov 17, 2011 at 4:01 pm
    Thanks, I will take a look at those articles on MOS. The funny thing is that my sample production machine looks quite clean, while various staging / dev machines do not; neither of them have had disk additions.

    -----Original Message-----
    From: Herring Dave - dherri
    Sent: Thursday, November 17, 2011 10:57 AM
    To: CRISLER, JON A; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    Jon,

    I'd like to claim all sorts of research behind that query but I'm sure it's based on key MOS articles: 818171.1 (Identifying Files with Imbalances), 351117.1 (Troubleshooting ASM Space Issues), 367445.1 (Advanced Balance and Space Report on ASM).

    Should you be performing manual rebalances? I would. Unfortunately I've never been given permission to schedule regular manual rebalances due to paranoia about our current release (10.2.0.2). We see the same increasing imbalance happening on production and since ASM never performs a rebalance unless you tell it to, it eventually should be addressed. We have a lot of growth so our rebalances are happening with regular disk adds.

    If you're curious enough, review results in ASM tracefiles for ARB* background processes. ARB tracefiles list every grouping of AUs moved by timestamp, per file, so a bit of parsing and loading as an external table would help you analyze how much is being moved per file per ARB process at what times. Higher power values increase the number of ARB processes that run concurrently on each file. I've tracked this trying to resolve why some systems have much greater differences in estimated work than actual work (some close to 200% more). Unfortunately my job demands keep getting in the way of doing fun research and actually blogging my results to share.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of CRISLER, JON A
    Sent: Wednesday, November 16, 2011 10:23 PM
    To: CRISLER, JON A; Herring Dave - dherri; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    Sorry, should have been "Dave" not John.

    -----Original Message-----
    From: CRISLER, JON A
    Sent: Wednesday, November 16, 2011 11:22 PM
    To: 'Dave.Herring@acxiom.com'; John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John- this was a nice query. I am trying to answer the question- do we need to run a manual rebalance? We have not added any disks, but my problem system has a huge amount of I/O and lots of file extents. Running your query, here are some results (just the top few)-

    FILE_NUM MAX_DISK_EXTENTS MIN_DISK_EXTENTS DISK_EXTENTS_IMBALANCE
    ---------- ---------------- ---------------- ----------------------
    271 147269 6136 141133
    272 63761 2657 61104
    297 25378 1057 24321
    298 20286 845 19441
    292 8656 361 8295
    277 8277 345 7932
    258 8025 334 7691
    263 8026 335 7691
    278 7838 327 7511
    274 7525 313 7212
    276 7525 313 7212

    The underlying disk group would be made up of perhaps 6 to 8 disks of about 1 tb each, one a SAN which is striped all over the place.



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Herring Dave - dherri
    Sent: Tuesday, November 15, 2011 9:30 AM
    To: John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • John Hallas at May 2, 2012 at 9:02 am
    I have blogged a bit more information on why disks get out of balance and how to address it.

    http://jhdba.wordpress.com/2012/05/02/curing-unevenly-balanced-asm-diskgroups-to-reduce-poor-file-distribution/

    John

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Herring Dave - dherri
    Sent: Tuesday, November 15, 2011 9:30 AM
    To: John.Hallas@morrisonsplc.co.uk; Oracle-L Group
    Subject: RE: ASM disks and how disks are balanced.

    John,

    Have you tried doing multiple rebalance operations in a row? From what I've seen (granted under 10.2.0.2), Oracle runs through each file by FILE_ID and tries to balance it across all available disks in the diskgroup. There's a chance that after one pass files won't be completely balanced, but I believe it only does 1 pass per rebalance operation.

    Have you checked individual files to see which are the most imbalanced? This may not lead directly to an answer for you on the original question but at least may better show what's going on and allow you to see files that get "balanced". The following query (you probably have something similar) shows you the top "imbalanced" files.

    SELECT file_num, MAX(extent_count) max_disk_extents, MIN(extent_count) min_disk_extents
    , MAX(extent_count) - MIN(extent_count) disk_extents_imbalance
    FROM (SELECT number_kffxp file_num, disk_kffxp disk_num, COUNT(xnum_kffxp) extent_count
    FROM x$kffxp
    WHERE group_kffxp = 1
    AND disk_kffxp != 65534
    GROUP BY number_kffxp, disk_kffxp
    ORDER BY number_kffxp, disk_kffxp)
    GROUP BY file_num
    HAVING MAX(extent_count) - MIN(extent_count) > 5
    ORDER BY disk_extents_imbalance DESC;

    Ideally after each rebalance operation you'll see less files with large imbalances.

    DAVID HERRING
    DBA
    Acxiom Corporation
    EML   dave.herring@acxiom.com
    TEL    630.944.4762
    MBL   630.430.5988
    1501 Opus Pl, Downers Grove, IL 60515, USA
    WWW.ACXIOM.COM

    The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of John Hallas
    Sent: Tuesday, November 15, 2011 5:45 AM
    To: Oracle-L Group
    Subject: ASM disks and how disks are balanced.


    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name, dg.allocation_unit_size/1024/1024
    /

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      11364      11470  11390
    DATA                                    1       8282      14258   9170

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      72659      72920  72837
    DATA                                    1      19377      30987  22157
    DATAMRDW                                8      10464      15536  11654


    NAME                                  MIN        MAX    AVG
    ------------------------------ ---------- ---------- ------
    DATA                                47056      50344  48131 -- before rebalance
    FRA                                 22740      22769  22752

    alter diskgroup data rebalance power 8;

    NAME                               AU(Mb)        MIN        MAX    AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA                                     1      22740      22769  22752
    DATA                                    8      47824      49096  48131 -- after rebalance  -  no real difference

    It is noticeable that the FRA disks for each database are evenly spaced with the min/max/avg all being closer together whereas DATA shows much more difference. This is probably because the archivelogs and flashbacks logs are of a uniform size.

    Rebalancing makes no difference. As can be seen from the last 2 examples.

    To make it clear I show a disk listing from the first database I listed above. That is not an unusual situation from what I can see but it does not fit in with 'evenly distributed' as per the documentation.

    1 0 MEMBER ONLINE NORMAL UNKNOWN 51200 8400 DATA_0000 DATA_0000
    1 1 MEMBER ONLINE NORMAL UNKNOWN 51200 8364 DATA_0001 DATA_0001
    1 2 MEMBER ONLINE NORMAL UNKNOWN 51200 8595 DATA_0002 DATA_0002
    1 3 MEMBER ONLINE NORMAL UNKNOWN 51200 8434 DATA_0003 DATA_0003
    1 4 MEMBER ONLINE NORMAL UNKNOWN 51200 9204 DATA_0004 DATA_0004
    1 5 MEMBER ONLINE NORMAL UNKNOWN 51200 9158 DATA_0005 DATA_0005
    1 6 MEMBER ONLINE NORMAL UNKNOWN 51200 9826 DATA_0006 DATA_0006
    1 7 MEMBER ONLINE NORMAL UNKNOWN 51200 9863 DATA_0007 DATA_0007
    1 8 MEMBER ONLINE NORMAL UNKNOWN 51200 9601 DATA_0008 DATA_0008
    1 9 MEMBER ONLINE NORMAL UNKNOWN 51200 9559 DATA_0009 DATA_0009
    1 10 MEMBER ONLINE NORMAL UNKNOWN 51200 11323 DATA_0010 DATA_0010 XXXXXXX
    1 11 MEMBER ONLINE NORMAL UNKNOWN 51200 14258 DATA_0011 DATA_0011 XXXXXXX
    1 13 MEMBER ONLINE NORMAL UNKNOWN 51200 8736 DATA_0013 DATA_0013
    1 14 MEMBER ONLINE NORMAL UNKNOWN 51200 8606 DATA_0014 DATA_0014
    1 15 MEMBER ONLINE NORMAL UNKNOWN 51200 8389 DATA_0015 DATA_0015
    1 16 MEMBER ONLINE NORMAL UNKNOWN 51200 8661 DATA_0016 DATA_0016
    1 17 MEMBER ONLINE NORMAL UNKNOWN 51200 8428 DATA_0017 DATA_0017
    1 18 MEMBER ONLINE NORMAL UNKNOWN 51200 8282 DATA_0018 DATA_0018
    1 19 MEMBER ONLINE NORMAL UNKNOWN 51200 8546 DATA_0019 DATA_0019
    1 20 MEMBER ONLINE NORMAL UNKNOWN 51200 8426 DATA_0020 DATA_0020
    1 21 MEMBER ONLINE NORMAL UNKNOWN 51200 8444 DATA_0021 DATA_0021
    1 22 MEMBER ONLINE NORMAL UNKNOWN 51200 8628 DATA_0022 DATA_0022

    So my question is, is this normal and expected (which I think it is based on my systems -all 11GR1, external redundancy ) or should we be seeing better disk balance than we are doing.

    John

    www.jhdba.wordpress.com

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l
  • John Hallas at Nov 15, 2011 at 3:41 pm
    Thanks Suse.
    This is a quiet test system so the rebalance level was OK at 8.
    I agree that it is not the most important thing in the world but I am only challenging the documentation that states that disks are rebalanced evenly. I can see that they are with FRA diskgroups but not with DATA ones.
    I am working on Dave's suggestion of multiple consecutive rebalances and I can see it makes a subtle difference (there are about 10 rebalance commands between the 3 snapshots shown below) but not a massive difference.
    NAME AU(Mb) MIN MAX AVG
    ------------------------------ ---------- ---------- ---------- ----------
    DATA 8 48528 51496 50278.1176
    DATA 8 48560 51496 50278.1176
    DATA 8 48912 51232 50278.1176

    I have also looked at the imbalance between files and I cannot see anything difference or improvement after doing the imbalances. Unfortunately the biggest difference I can see is on a production database diskgroup and I cannot experiment with that.

    John

    From: Suse Shi
    Sent: 15 November 2011 14:48
    To: John Hallas
    Cc: Oracle-L Group
    Subject: Re: ASM disks and how disks are balanced.

    Hi John,
    from your query, we can see the disks space in a very similar scope.

    I think it's expected and no worry at the difference on data, ASM instance will try to do rebalance jobs and keep space-usage 'SAME' on different disks, but it doesn't mean that ASM diskgroup will do rebalance once there're tiny modifications, that should be a waste of performance.

    If you're working on a test environment, just do add/drop disks, add/drop large files jobs and see the changes, I think the difference will always be in an acceptable scope.

    btw, if your systems are in busy transactions state, please don't set power value so high as '8', it will also decrease the database i/o performance.

    wish helps,

    -S
    2011/11/15 John Hallas <John.Hallas@morrisonsplc.co.uk

    Looking at a variety of systems I can see that there is a difference between the space available on each disk of a ASM disk group.
    This is despite the documentation stating :-

    If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances among all of the disks in a disk group, different ASM disks should not share the same physical drive.

    The following query shows a selection of databases and how the disks are striped. In all examples the disks in the disk group are the same size - normally 100Gb

    Select dg.name<http://dg.name>,dg.allocation_unit_size/1024/1024 "AU(Mb)",min(d.free_mb) Min, max(d.free_mb) Max, avg(d.free_mb) Avg
    from v$asm_disk d, v$asm_diskgroup dg
    where d.group_number = dg.group_number
    group by dg.name<http://dg.name>, dg.allocation_unit_size/1024/1024
    /

    NAME AU(Mb) MIN MAX AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA 1 11364 11470 11390
    DATA 1 8282 14258 9170

    NAME AU(Mb) MIN MAX AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA 1 72659 72920 72837
    DATA 1 19377 30987 22157<tel:30987%C2%A0%2022157>
    DATAMRDW 8 10464 15536 11654


    NAME MIN MAX AVG
    ------------------------------ ---------- ---------- ------
    DATA 47056 50344 48131<tel:50344%C2%A0%2048131> -- before rebalance
    FRA 22740 22769 22752

    alter diskgroup data rebalance power 8;

    NAME AU(Mb) MIN MAX AVG
    ------------------------------ ---------- ---------- ---------- ------
    FRA 1 22740 22769 22752
    DATA 8 47824 49096 48131 -- after rebalance - no real difference


    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    This email does not constitute a contract in writing for the purposes of the Law of Property (Miscellaneous Provisions) Act 1989.

    Our Standard Terms and Conditions of Purchase, as may be amended from time to time, apply to any contract that we enter into. The current version of our Standard Terms and Conditions of Purchase is available at: http://www.morrisons.co.uk/gscop

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 15, '11 at 11:45a
activeMay 2, '12 at 9:02a
posts8
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase