Quick post of a query I just used to diagnose a problem with a SQL statement that had a lot of db file sequential read waits. This uses ASH data to see which segment the waits were on the most.
select e.OWNER,e.SEGMENT_NAME,e.SEGMENT_TYPE,count(*) from (select P1 FILE_ID,P2 BLOCK_ID from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time between to_date('18-SEP-2012 08:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('18-SEP-2012 17:00:00','DD-MON-YYYY HH24:MI:SS') and sql_id='88h5hqqu6g0xs' and event='db file sequential read') bw,dba_extents e where bw.file_id=e.file_id and e.BLOCK_ID <=bw.block_id and e.BLOCK_ID+e.BLOCKS >bw.block_id group by e.OWNER,e.SEGMENT_NAME,e.SEGMENT_TYPE order by e.OWNER,e.SEGMENT_NAME,e.SEGMENT_TYPE;
The query represented by sql_id 88h5hqqu6g0xs is a delete and I knew it was doing a bunch of sequential read waits but I didn’t know on what segments. The query showed me that it was on all the indexes of the table being deleted from as well as the table itself.
If you want to use the query yourself change the sql_id to the sql_id for your query and change the date and times to match the range during which your problem occurred.