How to find historical sql_id executed by users

Question

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_ACTIVE_SESS_HISTORY,DBA_USERS and DBA_HIST_SNAPSHOTS to try find the username,sql_id,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?

Answer 1

AWR captures only the top N sql for each category (Elapsed time, CPU time, I/O time, Gets, Reads, etc.).

If you set this limit to MAXIMUM with DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, even that does not guarantee capturing all SQLs as they may age out from the shared pool between taking AWR snapshots.

If you want all SQLs to be logged, you can use SQL tracing (Monitoring and Tracing SQL) or Auditing (Monitoring Database Activity with Auditing) with the settings that fulfill your requirements.