Regarding ASM Disk statistics

Questions relating to Oracle Real Application Clusters (RAC) and Clusterware.

Moderator: Tim...

Regarding ASM Disk statistics

Postby sivakumarocp » Mon Jan 28, 2013 9:26 am

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.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: Regarding ASM Disk statistics

Postby Tim... » Mon Jan 28, 2013 9:46 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Regarding ASM Disk statistics

Postby sivakumarocp » Mon Jan 28, 2013 11:21 am

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 :(
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: Regarding ASM Disk statistics

Postby Tim... » Mon Jan 28, 2013 3:39 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle RAC and Clusterware

Who is online

Users browsing this forum: 1982 and 1 guest