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…
Kevin Closson's Blog: Platforms, Databases and Storage 
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
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
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
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.
…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
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,
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 🙂
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
Funny to find out the same script somewhere else… 😦
http://www.dba-oracle.com/t_monitor_io_pl_sql_gv_sysstat.htm
Hi Jean-Christophe,
Well, Don’s article credits the author, provides a link and then does full cut and paste.