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@xxxxxxxxxx'; John.Hallas@xxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Herring Dave - dherri
Sent: Tuesday, November 15, 2011 9:30 AM
To: John.Hallas@xxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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


Other related posts: