RE: ASM disks and how disks are balanced.

From: John Hallas <John.Hallas_at_morrisonsplc.co.uk>
Date: Wed, 2 May 2012 10:01:33 +0100
Message-ID: <EC65ECF8123FEE4D8FC5B212637C3040012D4076BA9E_at_EXCH1.morrisonsplc.co.uk>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri Sent: Tuesday, November 15, 2011 9:30 AM To: John.Hallas_at_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_at_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_at_freelists.org [mailto:oracle-l-bounce_at_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
Received on Wed May 02 2012 - 04:01:33 CDT

Original text of this message