Under the hood of ASM

With Oracle ASM the Oracle DBA has a new very powerfull storage medium where he/she can put the database files.
However ASM already was introduced in Oracle 10g, for a lot of DBA's it is still a black-box with only very small holes in it where you can peek in.

In the beginning, I must confess I was very suspicious about ASM, especially because of being a black-box.
However it occured to me very soon that it looks like a black-box because there is nothing much to see.
ASM is very limited in what it shows to the DBA, and even now I think the word 'limited' does not fit here. The word 'basic' is more fitting. ASM shows only basic information of what it is housing and what it is doing.
For showing this information ASM has a couple of views available. These are the only views available. In contradiction to a "normal" database instance, all dictionary views are not available in ASM instances.
The following views are available:

View Name	Description
V$ASM_DISKGROUP	Lists all available diskgroups with their characteristics
V$ASM_DISKGROUP_STAT	Synonym for v$asm_diskgroup
V$ASM_DISK	Lists all disks available to ASM that match the ASM disk_string parameter
V$ASM_DISK_STAT	Synonym for v$asm_disk
V$ASM_FILE	Contains an entry for every file stored in ASM
V$ASM_ALIAS	Lists the known file names that link to ASM files
V$ASM_CLIENT	Lists the databases that connect to this ASM instance
V$OPERATION	Lists long running ASM operations, like rebalance actions

Last week we had an issue, where ASM showed very high I/O activity on 2 of the 9 disks in a diskgroup. The behaviour could not be explained by the activity in the application. It looked like ASM was either doing a large rebalance on its own, or we were slowly running into a problem with the spreading of the data.

Unfortunately the above views can't give you an overview about where the data really is put, i.e. on which disk.

As everyone knows ASM uses striping technology, to spread the data across all disks.

It however looked like we had hit some hot block in a table(space) that was only stored on these two disks.

Using my most famous library "Google", after a while I found a kind of hidden view. That is, all above views have a system view which has the similar information.

There is however 1 view that hasn't a 'user readable' alias.

This view contains information about the striping of the database objects across the disks of a diskgroup. This view is called x$kffxp and it can be joined with the other views to get the needed information.

However our "problem" stopped after a couple of hours I did check the seperation of the serveral tablespace blocks across the disks. There was nothing abnormal found, however if there had been something abnormal, it might be that the "problem" was indeed a rebalance, solving this inbalance.

I used the following script to check the balance of the data across the disks:

set pages 9999 lines 200

column name format a40

select a.name, b.disk_kffxp disk, count(disk_kffxp) blocks

from

v$asm_alias a

, x$kffxp b

, v$asm_file c

where

a.group_number=b.group_kffxp

and a.group_number=c.group_number

and a.file_number=c.file_number

and a.file_number=b.number_kffxp

and c.type in ('DATAFILE','TEMPFILE','ONLINELOG')

group by a.name, b.disk_kffxp

order by a.name, count(disk_kffxp) desc;

Output then could look like ( for the SYSAUX tablespace ):

NAME	DISKNR	BLOCKS
SYSAUX.338.715000059	6	242
SYSAUX.338.715000059	7	229
SYSAUX.338.715000059	2	218
SYSAUX.338.715000059	5	216
SYSAUX.338.715000059	0	213
SYSAUX.338.715000059	1	211
SYSAUX.338.715000059	4	211
SYSAUX.338.715000059	3	207
SYSAUX.338.715000059	8	181

Conclusion:

The SYSAUX tablespace is reasonably balanced, although the difference between disk 8 and disk 6 is more than 40 blocks stored.