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.

Thursday, October 14, 2010

NOTE: Unident of disk

In our tracefile of the ASM rebalancer process, every 5 minutes the following line is shown:

NOTE:Unident of disk:/dev/asmspfile

There is not really much information to be found on what this can mean, even on My Oracle Support there is no information found.

I am guessing this message is explained by the fact that the diskname matches our asm_diskstring:

SQL> show parameter asm_diskstring

asm_diskstring string /dev/asm*

but in fact is not part of any (active) diskgroup
In our case this disk, or raw device, contains the shared spfile of the ASM instances across our RAC nodes.

If this assumption is true, then this message can be safely ignored.

What is the risk:

If you don't keep a good administration of what disks have which function, or you mess up with the disk naming convention anyway, the risk exists that you'll add disks to a diskgroup that might be in use for other functions.
Like in our case, if I would not know this disk contains the ASM spfile, it would be very easy to add that disk to a diskgroup. And I doubt if ASM would complain doing so.