Search this Blog

Friday, October 15, 2010

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 NameDescription
V$ASM_DISKGROUPLists all available diskgroups with their characteristics
V$ASM_DISKGROUP_STATSynonym for v$asm_diskgroup
V$ASM_DISKLists all disks available to ASM that match the ASM disk_string parameter
V$ASM_DISK_STATSynonym for v$asm_disk
V$ASM_FILEContains an entry for every file stored in ASM
V$ASM_ALIASLists the known file names that link to ASM files
V$ASM_CLIENTLists the databases that connect to this ASM instance
V$OPERATIONLists 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, b.disk_kffxp disk, count(disk_kffxp) blocks
v$asm_alias a
, x$kffxp b
, v$asm_file c
and a.group_number=c.group_number
and a.file_number=c.file_number
and a.file_number=b.number_kffxp
group by, b.disk_kffxp
order by, count(disk_kffxp) desc;

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

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

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


  1. Yes there is inbalance we can found using this beautiful query.
    But how can we do adjust this rebalance to make the performance good.

  2. Hi,

    Since I don't know what you mean by "inbalance" in terms of how much, I can only suggest to do a manual rebalance yourself, using the statement: