Regarding ASM Disk statistics

[sivakumarocp]

Hi Tim,

In one of our Production host (Linux) there are 5 databases are running under 2 different diskgroups.
In that we are seeing huge I/O on DATAFILE_ORPD88 diskgroup. upon checking further we found a SQL from orprd88b & orprd88c database causing this I/O and we suggested application team to create index for that SQL statement.

meanwhile I taken IO statistics report for the diskgroups in that ASM instance it shown like below.
in that Bytes read(MB) columns shows value for orprd88c database to nearly 2 TB!!

Code: Select all

                                                               bytes                        write     bytes
                                     disk    read     read     read      disk      write     time   written
DBNAME   Group_name         Disk#    reads  errors    time(s)  (mb)    writes      errors     (s)      (mb)
-------- ------------------ ----- ----------- ------- ------- ----------- --------- ------- ------- ---------
orprd88a DATAFILE_ORPD88     4    50239822       0      32    459046.1    273427       0       3    7229.2
orprd88a DATAFILE_ORPD88     3    51357240       0      33    468438.9    286815       0      13    8043.2
orprd88a DATAFILE_ORPD88     5    50869916       0      31    464943.4    296314       0       3    7320.3
orprd88a DATAFILE_ORPD88     0    51251197       0      31    468247.6    240891       0       2    6924.0
orprd88b DATAFILE_ORPD88     5    26834289       0       4   1127809.0    107981       0       0    1316.9
orprd88b DATAFILE_ORPD88     4    27191079       0       5   1155130.2     97297       0       0    1302.0
orprd88b DATAFILE_ORPD88     3    32308065       0       5   1275525.0    104476       0       0    1293.5
orprd88b DATAFILE_ORPD88     0    32311317       0       5   1377719.4     94676       0       0    1228.3
orprd88c DATAFILE_ORPD88     0   259181840       0       9   2041991.5     66905       0       0    6588.3
orprd88c DATAFILE_ORPD88     3   250998002       0      10   1980292.5     63435       0       0    6534.9
orprd88c DATAFILE_ORPD88     5   255889061       0       8   2018168.2     62153       0       0    6487.7
orprd88c DATAFILE_ORPD88     4   248658177       0       9   1962937.0     53762       0       0    6346.4
orprd7d DATAFILE             2       91236       0       0      3809.6     55389       0       1     955.1
orprd7d DATAFILE             3       89949       0       0      3825.2     38640       0       0     541.5
orprd7p DATAFILE             2      766485       0       2     68777.3    720585       0       3    8799.0
orprd7p DATAFILE             3      757538       0       2     68612.2    719075       0       5    9288.8


What does this 2TB Bytes read means. Whether its a cumulative read value or it has been counted since the diskgroup created.

I am not clear with this, please clarify.

[Tim...]

Hi.

How did you generate the report? That really dictates what this means.

If the statistics are coming from v$asm_disk, the the values are cumulative since the last restart, meaning it is the total or operations since the ASM instance was last started. That is not a very useful figure.

Cheers

Tim...

[sivakumarocp]

Thanks for the update.

Then how do i get the CURRENT IO statistics for each diskgroup?

I want to figure it out to the customer that huge IO is happening on this diskgroup, how can i achieve this.

V$ASM_DISKGROUP_STAT has not much info what i expect

[Tim...]

Hi.

You can take the approach that is used by statspack age AWR, which is to capture the stats on a regular interval and check the difference between them. Calculating the deltas like this allow you to see the change over time.

Cheers

Tim...