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.
– Bobby
