select distinct ash.event, ash.sql_id, users.username, ash.program, dbms_lob.substr(sql_text.sql_text, 4000, 1) sql_text from dba_hist_active_sess_history ash left outer join dba_users users on ash.user_id = users.user_id left outer join dba_hist_sqltext sql_text on ash.sql_id = sql_text.sql_id where ash.snap_id between BEGIN_SNAP_ID and END_SNAP_ID --REPLACE THE BEGIN AND END SNAPSHOT ID HERE and ash.event = 'wait event'
dba_hist_active_sess_history
dba_users
dba_hist_sqltext
BEGIN_SNAP_ID
END_SNAP_ID
--SQL TO RETRIVE SNAPSHOT IDS select DBID, INSTANCE_NUMBER, SNAP_ID, BEGIN_INTERVAL_TIME from DBA_HIST_SNAPSHOT order by 4 asc; Begin Snapshot ID: 1000 End Snapshot ID: 1010 Wait Event: 'db file sequential read'
'db file sequential read'
select distinct ash.event, ash.sql_id, users.username, ash.program, dbms_lob.substr(sql_text.sql_text, 4000, 1) sql_text from dba_hist_active_sess_history ash left outer join dba_users users on ash.user_id = users.user_id left outer join dba_hist_sqltext sql_text on ash.sql_id = sql_text.sql_id where ash.snap_id between 1000 and 1010 and ash.event = 'db file sequential read'