Monitor RAC I/O with gv$iostat_function

I was delivering an 11gR2 New Features course, followed by an 11gR2 RAC accelerated course. That lead me to combine the not so widely known new view v$iostat_function with the RAC area :-)

We can now very easy monitor what DB function is doing I/O at what instances in our RAC DB:

SQL> select inst_id,function_name,
sum(small_read_megabytes+large_read_megabytes) as read_mb,
sum(small_write_megabytes+large_write_megabytes) as write_mb
from gv$iostat_function
group by cube (inst_id,function_name) 
order by inst_id,function_name;
  INST_ID FUNCTION_NAME         READ_MB   WRITE_MB
---------- ------------------ ---------- ----------
         1 ARCH                        0          0
         1 Archive Manager             0          0
         1 Buffer Cache Reads        610          0
         1 DBWR                       12        373
         1 Data Pump                   0          0
         1 Direct Reads             1162          1
         1 Direct Writes               1        167
         1 LGWR                        1        346
         1 Others                   5215       2116
         1 RMAN                        0          0
         1 Recovery                    0          0
         1 Smart Scan                  0          0
         1 Streams AQ                  1          0
         1 XDB                         0          0
         1                          7002       3003
         2 ARCH                        0          0
         2 Archive Manager             0          0
         2 Buffer Cache Reads        187          0
         2 DBWR                       11        520
         2 Data Pump                   0          0
         2 Direct Reads                6          0
         2 Direct Writes               0          0
         2 LGWR                        1        299
         2 Others                   3898       1030
         2 RMAN                        0          0
         2 Recovery                    0          0
         2 Smart Scan                  0          0
         2 Streams AQ                  1          0
         2 XDB                         0          0
         2                          4104       1849
         3 ARCH                        0          0
         3 Archive Manager             0          0
         3 Buffer Cache Reads        131          0
         3 DBWR                        2         79
         3 Data Pump                   0          0
         3 Direct Reads                0          0
         3 Direct Writes               0          0
         3 LGWR                        0         58
         3 Others                   1140        269
         3 RMAN                        0          0
         3 Recovery                    0          0
         3 Smart Scan                  0          0
         3 Streams AQ                  0          0
         3 XDB                         0          0
         3                          1273        406
           ARCH                        0          0
           Archive Manager             0          0
           Buffer Cache Reads        928          0
           DBWR                       25        972
           Data Pump                   0          0
           Direct Reads             1168          1
           Direct Writes               1        167
           LGWR                        2        703
           Others                  10253       3415
           RMAN                        0          0
           Recovery                    0          0
           Smart Scan                  0          0
           Streams AQ                  2          0
           XDB                         0          0
                                   12379       5258

60 rows selected.

This is a 3 node cluster with not so much I/O done yet. Especially no Data Pump related I/O. We change that:

SQL> create directory dpdir as '+data';

Directory created.

SQL> create directory logdir as '/home/oracle/logdir';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@host01 ~]$ mkdir /home/oracle/logdir

expdp full=y directory=dpdir logfile=logdir:mylog.txt
-- Output not shown --
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  +DATA/expdat.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 18:00:33

The dumpfile was created into the ASM diskgroup DATA here, while the logfile was placed in a conventional directory on the local node. When we look into the diskgroup, we see that an alias was created:

ASMCMD> ls data -al
Type Redund  Striped  Time Sys  Name
                           Y    ORCL/
                           Y    cluster01/
                           N    expdat.dmp => +DATA/ORCL/DUMPSET/SYSSYS_EXPORT_FULL_01_74638_1.272.774121815

gv$iostat_function does show that Data Pump related I/O now:

SQL> select inst_id,function_name,
sum(small_read_megabytes+large_read_megabytes) as read_mb,
sum(small_write_megabytes+large_write_megabytes) as write_mb
from gv$iostat_function
group by cube (inst_id,function_name) 
order by inst_id,function_name;
   INST_ID FUNCTION_NAME         READ_MB   WRITE_MB
---------- ------------------ ---------- ----------
         1 ARCH                        0          0
         1 Archive Manager             0          0
         1 Buffer Cache Reads        770          0
         1 DBWR                       14        425
         1 Data Pump                 795        540
         1 Direct Reads             1194          1
         1 Direct Writes               1        167
         1 LGWR                        1        451
         1 Others                   5297       2131
         1 RMAN                        0          0
         1 Recovery                    0          0
         1 Smart Scan                  0          0
         1 Streams AQ                  1          0
         1 XDB                         0          0
         1                          8073       3715
         2 ARCH                        0          0
         2 Archive Manager             0          0
         2 Buffer Cache Reads        191          0
         2 DBWR                       13        541
         2 Data Pump                   0          0
         2 Direct Reads                6          0
         2 Direct Writes               0          0
         2 LGWR                        1        309
         2 Others                   3955       1044
         2 RMAN                        0          0
         2 Recovery                    0          0
         2 Smart Scan                  0          0
         2 Streams AQ                  1          0
         2 XDB                         0          0
         2                          4167       1894
         3 ARCH                        0          0
         3 Archive Manager             0          0
         3 Buffer Cache Reads        142          0
         3 DBWR                        4         83
         3 Data Pump                   0          0
         3 Direct Reads                0          0
         3 Direct Writes               0          0
         3 LGWR                        0         68
         3 Others                   1233        283
         3 RMAN                        0          0
         3 Recovery                    0          0
         3 Smart Scan                  0          0
         3 Streams AQ                  0          0
         3 XDB                         0          0
         3                          1379        434
           ARCH                        0          0
           Archive Manager             0          0
           Buffer Cache Reads       1103          0
           DBWR                       31       1049
           Data Pump                 795        540
           Direct Reads             1200          1
           Direct Writes               1        167
           LGWR                        2        828
           Others                  10485       3458
           RMAN                        0          0
           Recovery                    0          0
           Smart Scan                  0          0
           Streams AQ                  2          0
           XDB                         0          0
                                   13619       6043

60 rows selected.

In this case, all the Data Pump I/O was done by the Instance 1.  There is also a relation to Exadata, when you look at the function Smart Scan :-)

 

Addendum: The Data Pump export fails if the log file is not redirected out of the ASM diskgroup:

[oracle@host01 ~]$ expdp tables=scott.dept directory=dpdir

Export: Release 11.2.0.1.0 - Production on Thu Feb 2 17:42:37 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

 

Therefore I used a local directory – which is less elegant than using an ACFS based directory:

SQL> drop directory logdir;

Directory dropped.

SQL> create directory logdir as '/u01/app/grid/acfsmounts/data_myvol/logdir';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@host01 ~]$ expdp tables=scott.dept directory=dpdir logfile=logdir:mylog.txt
Export: Release 11.2.0.1.0 - Production on Thu Feb 2 17:06:46 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

-- output not shown --
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  +DATA/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 17:07:26

On Exadata, we may use DBFS instead.

About these ads

, , ,

  1. #1 by kevinclosson on February 1, 2012 - 19:43

    Hi Uwe,

    Any way to get the IOPS out of that?

  2. #2 by Ranjit Nagi on February 1, 2012 - 20:19

    Hi, I saw null in function column and have high read and write.
    Who caused this?

    Regards
    Ranjit

  3. #3 by Uwe Hesse on February 1, 2012 - 21:09

    Hi Kevin, thank you for stopping by :-) I don’t think so. When you look at the description

    http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2010.htm#I1030442

    there is only NUMBER_OF_WAITS and WAIT_TIME (ms), so I don’t see how we could get I/O per second from that view.

  4. #4 by Uwe Hesse on February 1, 2012 - 21:14

    Ranjit, thanks for asking the question! The null rows are subtotals for the Instances and totals for the whole Database. In a previous life, I was developer, amongst others programming lists containing those subtotals and totals – that was very boring and quite some effort. Much easier with the nice cube function :-)

  5. #5 by kevinclosson on February 2, 2012 - 00:17

    Sad…that would be great value add!

  6. #6 by Noons on February 2, 2012 - 04:01

    Quick one: rather than sorting by inst_id and then function_name, wouldn’t it be easier to read if the instances were grouped together by db I/O?
    As in: sort by function_name and then by inst_id?
    Given that we’re mostly interested in the instance doing more of a given db I/O, this would group the data for each db I/O and make it easier to spot a “hot” instance?

  7. #7 by Uwe Hesse on February 2, 2012 - 10:11

    Kevin, sorry :-(

    Noons, sure, why not. Looks also useful – I like the other sort better, but that is just a matter of taste, probably :-)

  8. #8 by Gennadiy on February 4, 2012 - 15:40

    Hi Uwe, Kevin,

    is the V$IOSTAT_FUNCTION_DETAIL view what you are looking for, in order to get IOPS numbers (SMALL_READ_REQS,LARGE_READ_REQS,SMALL_WRITE_REQS,LARGE_READ_REQS)?

    Regards,
    Gennadiy.

  9. #9 by Uwe Hesse on February 7, 2012 - 13:17

    Gennadiy,
    could you tell me how you compute IOPS from these numbers?

  10. #10 by Gennadiy on February 7, 2012 - 16:42

    sorry Uwe, my guess was wrong – I think the gV$IOFUNCMETRIC is the rigth place for looking into IOPS numbers.

  11. #11 by Paul Lynch on April 30, 2012 - 21:55

    v$iostat_function did not compare well to v$system_event and v$sysstat in a couple of databases I looked at. “Direct Reads” was close to the sum of “direct path read” and “direct path read temp” in v$system_event. “Others” and “Direct Writes” didn’t correspond to anything. “Buffer Cache Reads” was not even close to “session logical reads” or any other similar item in v$sysstat.

  12. #12 by bdrouvot on May 7, 2014 - 07:31

    Uwe, Kevin to get the number of IO per second from this view you can basically takes a snapshot each second from the cumulative view and computes the delta with the previous snapshot.

    So the difference of the NUMBER_OF_WAITS (or _REQS fields) / (time waited between 2 snapshots) would give the number of I/O per second ;-)

    I did it for:

    gv$filestat and gv$tempstat here: http://bdrouvot.wordpress.com/db_io_metrics_script/
    and
    gv$iostat_file here: http://bdrouvot.wordpress.com/db_io_type_metrics_script/

    I have in mind to do it for v$iostat_function as well, I googled “v$iostat_function” and as always your blog appears Uwe ;-)

    Bertrand

  13. #13 by Uwe Hesse on May 7, 2014 - 19:26

    Thank you for sharing that information here, Bertrand! Yes, seems like google knows me ;-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,760 other followers

%d bloggers like this: