Tony, I wrote the following PL/SQL program to extract the LIO per snapshot from existing AWR Data, and it takes Database restarts into account. You can modify it to extract anything you want from the AWR easily.....I have highlighted the pieces that do this. In fact I wrote this originally for extracting data from STATSPACK and modified it for AWR :) Hth, John Kanagaraj <>< DB Soft Inc http://www.linkedin.com/in/johnkanagaraj http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** rem rem Show diff in specific statistic from AWR Data rem Rewritten for 10g from Statspack rem JK/Sep 2007 declare v_lio number; v_begin_id sys.wrh$_sysstat.snap_id%TYPE; v_end_id sys.wrh$_sysstat.snap_id%TYPE; v_begin_time sys.wrm$_snapshot.begin_interval_time%TYPE; v_end_time sys.wrm$_snapshot.end_interval_time%TYPE; v_begin_startup_time sys.wrm$_snapshot.startup_time%TYPE; v_end_startup_time sys.wrm$_snapshot.startup_time%TYPE; l_FileHandle UTL_FILE.FILE_TYPE; l_FileDir Varchar2(100) := '/tmp'; l_FileName Varchar2(100) := ''; /* This cursor fetches details of the current snapshot plus the next one using the LEAD function. We will use this to make sure that there was no DB restart inbetween */ cursor snapshot is select snap_id, lead(snap_id, 1, 0) OVER (ORDER BY snap_id), startup_time, lead(startup_time, 1) OVER (ORDER BY snap_id), begin_interval_time, end_interval_time from sys.wrm$_snapshot; cursor sysstat is select e.value - b.value from sys.wrh$_sysstat b, sys.wrh$_sysstat e, v$statname n where b.stat_id = n.stat_id and b.stat_id = e.stat_id and n.name = 'session logical reads' and b.snap_id = v_begin_id and e.snap_id = v_end_id; begin -- dbms_output.enable (9999999); l_FileName := 'AWR_session_logical_reads.dat'; l_FileHandle := utl_file.fopen(l_FileDir, l_FileName, 'w'); open snapshot; LOOP fetch snapshot into v_begin_id, v_end_id, v_begin_startup_time, v_end_startup_time, v_begin_time, v_end_time; exit when snapshot%NOTFOUND; -- Run through only if the startup times for both snaps are same! -- also, avoid the last line (lead will return 0 for end_id) if ( v_begin_startup_time = v_end_startup_time ) and ( v_end_id != 0 ) then open sysstat; loop fetch sysstat into v_lio; exit when sysstat%NOTFOUND; utl_file.put_line(l_FileHandle, v_end_time || ',' || v_lio); end loop; close sysstat; end if; END LOOP; close snapshot; utl_file.fclose(l_FileHandle); end; / On 9/24/07, Tony Adolph <tony.adolph.dba@xxxxxxxxx> wrote: > > No I didn't, that would bugger up the results as you say :-( > > So the scrips not so generic, but still useful if you suspect you're > suffering from intermittent slow IO.... or persistently slow IO. > > I'll bear in mind database bounces for version 3 :-) > > Cheers > Tony >