I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view
DBA_HIST_SNAPSHOTS to try find the
sample_time etc.. but after some tests neither all sql executed was listed in output of the following query
col username for a30; col BEGIN_INTERVAL_TIME for a30; col END_INTERVAL_TIME for a30; COL SAMPLE_TIME FOR A30; set linesize 2000; select * from ( select b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME,u.username,a.SESSION_ID,a.SESSION_SERIAL#, a.INSTANCE_NUMBER,a.sql_id,A.SAMPLE_TIME from DBA_HIST_ACTIVE_SESS_HISTORY a inner join dba_users u on u.user_id=a.user_id inner join dba_hist_snapshot b on b.snap_id=a.snap_id where b.snap_id >= 40 and b.snap_id <= 46 order by BEGIN_INTERVAL_TIME desc) where rownum <= 50;
Why is this happening? Is there some other way to do it?