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…