Re: Datafile and disk path for ASM

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Mon, 15 Oct 2012 13:44:51 +0530
Message-ID: <OF1E41282E.E161B320-ON65257A98.002CC9B1-65257A98.002D3DB2_at_ibsplc.com>

Hi Martin,
Thanks for the advice.
From the EM 12c host monitoring pages, I am not seeing high I/O on this disk, but disk service time is shown as very high value. I/O remained the same as before the problem window. During the problem window we saw all I/O operations were taking so much time. DWR, CKPT , LGWR were top waiting sessions.

There was a suspected SQL run on of the database during this time. I am trying to figure out what complex data script has asked for. Only wait event I could see is 'db_file_sequential_read'.

Probably the data which resides on the disk is stored in such a fashion that SAN is taking so much time to fetch ?

With Regards,
Sreejith

--

Sreejith S Nair
Sr Database Administrator

From: Martin Berger <martin.a.berger_at_gmail.com>

To:     Sreejith.Sreekantan_at_ibsplc.com
Cc:     oracle-l_at_freelists.org
Date:   10/12/2012 10:19 PM
Subject:        Re: Datafile and disk path for ASM

Sreejith,

there is no simple mapping DISK <-> datafile.

I try to summarize it a little bit:
Every diskgroup can have many physical disks. Every disk in a diskgroup is split into equal pieces - Allocation Units (AU)
Every file in a diskgroup is spread evenly across different AUs on all disks available.
With redundancy it's even more complex.

So generally speaking, your disk sd9 will probably contain pieces of all datafiles within the diskgroup.

You should see a similar utilisation of all disks within the diskgroup.

If you really see only this particular disk with high IOs & queueing, you must figure out your hot block.

Maybe this will help as a starting point:

http://asmsupportguy.blogspot.co.at/2011/06/asm-file-extent-map.html  

hth
 Martin

On Fri, Oct 12, 2012 at 8:27 AM, Sreejith S Nair < Sreejith.Sreekantan_at_ibsplc.com> wrote: Hi List,
I am sure people may have already thought about this and have a solution, I am unable to find that info now !

So we have few 11.2.0.2 databases monitored by EM 12c on a two node RAC. We got an alert for one disk sd9 , 98 % busy from Host monitoring capabilities of EM.
I am trying to figure out what all datafiles ( of databases) are in this disk.

I got the slice which corresponds to this disk(sd9) from our SA. I checked v$asm_disk where I can see the PATH, disk group etc. I can see all datafile names from v$asm_alias.

But I could not understand how I will be able to see what all datafiles of particular databases are in this disk. Then I will be able to understand which all datafile writes/reads are contributing to this.

Thanks in advance.

With Regards,
Sreejith

--

Sreejith
Sr Database Administrator
sreejithsna.wordpress.com

DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 15 2012 - 10:14:51 CEST