ASH query for sequential reads

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply