How To Produce Raw, Spreadsheet-Ready Physical I/O Data With PL/SQL. Good For Exadata, Good For Traditional Storage.

Several folks who read the Winter Corporation Exadata Performance Assessment have asked me what method I used to produce the throughput timeline graphs. I apologize to them for taking so long to follow this up.

The method I used to produce that data is a simple PL/SQL loop that evaluates differences in gv$sysstat contents over time and produces its output into a file in the filesystem with appending writes. Of course there are a lot of other ways to get this data not the least of which include such tools as ASH and so forth. However, in my opinion, this is a nice technique to get raw data that is in an up-loadable format ready for Excel. Er, uh, I suppose I’m supposed to say OpenOfficeThisOrThatExcelLookAlikeThingy aren’t I? Oh well.

The following is a snippet of the output from the tool. This data was collected during a very lazy moment of SQL processing using a few Exadata Storage Server cells as the database storage. I simply tail(1) the output file to see the aggregate physical read and write rate in 5-second intervals. The columns are (from left to right) time of day, total physical I/O, physical read, physical write. Throughput values are in megabytes.


$ tail -f /tmp/mon.log
11:51:43|293|185|124|
11:51:49|312|190|102|
11:51:55|371|234|137|
11:52:00|360|104|257|
11:52:06|371|245|145|
11:52:11|378|174|217|
11:52:16|377|251|122|
11:52:21|431|382|83|
11:52:26|385|190|180|
11:52:32|244|127|140|
11:52:37|445|329|106|
11:52:42|425|301|101|
11:52:47|391|214|177|
11:52:53|260|60|200|

The following is the PL/SQL script. This script should be cut-and-paste ready to go.


set serveroutput on format wrapped size 1000000

create or replace directory mytmp as '/tmp';

DECLARE
n number;
m number;

gb number := 1024 * 1024 * 1024;
mb number := 1024 * 1024 ;

bpio number; -- 43 physical IO disk bytes
apio number;
disp_pio number(8,0);

bptrb number; -- 39 physical read total bytes
aptrb number;
disp_trb number(8,0);

bptwb number; -- 42 physical write total bytes
aptwb number;
disp_twb number(8,0);

x number := 1;
y number := 0;
fd1 UTL_FILE.FILE_TYPE;
BEGIN
        fd1 := UTL_FILE.FOPEN('MYTMP', 'mon.log', 'w');

        LOOP
                bpio := 0;
                apio := 0;

                select  sum(value) into bpio from gv$sysstat where statistic# = '43';
                select  sum(value) into bptwb from gv$sysstat where statistic# = '42';
                select  sum(value) into bptrb from gv$sysstat where statistic# = '39';

                n := DBMS_UTILITY.GET_TIME;
                DBMS_LOCK.SLEEP(5);

                select  sum(value) into apio from gv$sysstat where statistic# = '43';
                select  sum(value) into aptwb from gv$sysstat where statistic# = '42';
                select  sum(value) into aptrb from gv$sysstat where statistic# = '39';

                m := DBMS_UTILITY.GET_TIME - n ;

                disp_pio := ( (apio - bpio)   / ( m / 100 )) / mb ;
                disp_trb := ( (aptrb - bptrb) / ( m / 100 )) / mb ;
                disp_twb := ( (aptwb - bptwb) / ( m / 100 )) / mb ;

                UTL_FILE.PUT_LINE(fd1, TO_CHAR(SYSDATE,'HH24:MI:SS') || '|' || disp_pio || '|' || disp_trb || '|' || disp_twb || '|');
                UTL_FILE.FFLUSH(fd1);
                x := x + 1;
        END LOOP;

        UTL_FILE.FCLOSE(fd1);
END;
/

So, while it isn’t rocket-science, I hope it will be a helpful tool for at least a few readers and the occasional wayward googler who stops by…

14 Responses to “How To Produce Raw, Spreadsheet-Ready Physical I/O Data With PL/SQL. Good For Exadata, Good For Traditional Storage.”


  1. 1 Jeff Moss April 30, 2009 at 7:31 am

    Are you sure about statistic# 43 being “physical IO disk bytes”?

    In 10gR2 on AIX I ran:

    SELECT * FROM v$statname WHERE statistic# IN(39,42,43)
    /

    And it gave:

    39 physical read total bytes 8 2572010804
    42 physical write total bytes 8 2495644835
    43 IPC CPU used by this session 32 4247517299

    39 and 42 match but 43 doesn’t.

    Am I looking in the wrong place or is that a typo?

    I can’t find any trace of “physical IO disk bytes” in v$statname.

    Cheers
    Jeff

    • 2 kevinclosson April 30, 2009 at 3:44 pm

      Ugh…the title says good for oracle, good for conventional storage but doesn’t say which version… I just sort of figured folks associate me with Exadata and thus 11g (see the following) …

      SQL*Plus: Release 11.1.0.7.0 – Production on Thu Apr 30 08:42:17 2009

      Copyright (c) 1982, 2008, Oracle. All rights reserved.

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options

      SQL> SELECT * FROM v$statname WHERE statistic# IN(39,42,43);

      STATISTIC# NAME
      ———- —————————————————————-
      CLASS STAT_ID
      ———- ———-
      39 physical read total bytes
      8 2572010804

      42 physical write total bytes
      8 2495644835

      43 physical IO disk bytes
      64 3277619920

  2. 3 Jeff Moss April 30, 2009 at 6:48 pm

    Aha…I did think it might be that but didn’t have access to an 11g at the time…I was also thinking the statistic numbers wouldn’t change between releases…that’s the kind of thing that will upset lots of scripts, whether they be for diagnosis like this or, perhaps, for production usage.

    Interesting and educational.

    Yes, we definitely associate you with Exadata and therefore 11g, but we’ve also heard you have a “pretty good” track record with Oracle leading up to that!

    Cheers
    Jeff

    • 4 kevinclosson April 30, 2009 at 8:03 pm

      Ha…well, looking back I’m quite sure my port-level work on 5.1.17 was less than genius…but I think I’ve improved over time 🙂

      As for STATISTIC#…I do see your point…but that’s why we want to join and use statistic_name as the anchor I suppose…

      I hope snacking on rapidly changing values and being able to snoop the pipe-delimited file is helpful…it’s low-level, but I hope helpful… and if we are lucky someone really smart like Tanel will whip some really crafty PL/SQL extra magic in there for us 🙂

      BTW, I have measured the perturbation this method applies to CPU-saturated workloads and it is undetectable. So, enjoy.

  3. 5 Jeff Moss May 1, 2009 at 7:52 am

    …Generally, I’d have said it’s not the done thing to anchor to a description…it may change, even subtly…usually you’d join to an ID that “anchors”…and that’s my point…it appears that it isn’t an anchor between releases and that’s not helpful…2 of the 3 stayed the same, but one didn’t.

    I wrote a script which does this kind of thing via AWR…much less granular than your 5s script but possibly helpful to some…

    http://www.oramoss.com/wiki/index.php/Get_awr_io_stats

    As you say, I’m sure Tanel will come up with some “really craft PL/SQL extra magic”. 😉

    Cheers
    Jeff

    • 6 Mathew Butler May 15, 2009 at 9:21 am

      Hi Kevin/Jeff,

      I was just reading the Oracle Reference and came across the text below which reminded me of this discussion, so I’ve called back to add in the link.

      “Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications.”

      Oracle® Database Reference
      10g Release 2 (10.2)
      Part Number B14237-04

      http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2.htm#I1030264

      Best Regards,

      • 7 kevinclosson May 15, 2009 at 2:02 pm

        Thanks for the info Matthew…I just committed to memory very early on that I have to think of these two internal performance view joins in the reverse. Generally, codes would remain constant allowing descriptive text to change. For example, city codes are much friendlier to deal with…how would you like to type in “El Pueblo de Nuestra Señora la Reina de los Ángeles de la Porciúncula” as a city to join to another table 🙂

  4. 8 Adrian May 12, 2009 at 9:41 am

    Its very interesting to see the I/O from the database point of view vs what the os is showing using IOSTAT. When running performance tests ourselves we have seen that the MB/s shown using the oracle system statistic does compare with what IOSTAT is showing.

    I do have one question in a related area. What are your thoughts regarding the use of the following statistic.

    physical write total IO requests
    physical read total IO requests
    physical read total multi block requests
    physical write total multi block requests
    redo writes

    I have been using these for 10 minute snapshots along with IOSTAT data for the same period. The values are generally a lot lower than what IOSTAT is showing. Is this due to the translation between the databases I/O requests and what happens at lower levels?.

    Also what are your general thoughts on the use of these statistics?.

    Thanks & Regards


  1. 1 The danger of averages – Measuring I/O throughput « RNM Trackback on September 14, 2010 at 11:39 am
  2. 2 Graphing I/O data using gnuplot and Oracle V$SYSSTAT « RNM Trackback on October 26, 2010 at 2:43 pm
  3. 3 Comparing methods for recording I/O – V$SYSSTAT vs HP Measureware « RNM Trackback on March 9, 2011 at 1:39 pm
  4. 4 stat | Oracleguy's Blog Trackback on August 8, 2013 at 8:25 pm

Leave a Reply

Please log in using one of these methods to post your comment:

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




DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,944 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: