Note: Using AWR data requires you to have the Diagnostics Pack license option purchased. It may be possible to run the queries below without having the necessary license. Be sure to check with your DBA/licensing manager before using these.
The Automatic Workload Repository (AWR) contains details of the activity on your database. These are stored in various dba_hist* views.
The kind of information you're looking for is primarily available in the dba_hist_sqlstat view. By joining this to dba_hist_snapshot you can get the information available in a given period:
select *
from dba_hist_sqlstat t, dba_hist_snapshot s
where t.snap_id = s.snap_id
and t.dbid = s.dbid
and t.instance_number = s.instance_number
and s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats
This view holds various *_elapsed columns, which state the increase in the given snap (time period). e.g.:
- ELAPSED_TIME_DELTA
- CPU_TIME_DELTA
- ROWS_PROCESSED_DELTA
- BUFFER_GETS_DELTA
- DISK_READS_DELTA
- PARSE_CALLS_DELTA
- EXECUTIONS_DELTA
Times are in microseconds. You can combine these to get average time/execution stats and so on. For example, this gives you the average time/execution for a given sql statement:
select sql_id, sum(t.elapsed_time_delta/1000000)/sum(t.executions_delta)
from dba_hist_sqlstat t, dba_hist_snapshot s
where t.snap_id = s.snap_id
and t.dbid = s.dbid
and t.instance_number = s.instance_number
and s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats
group by sql_id
The sql_id is a hash of the text of the SQL itself. You can find the actual statement from:
select * from dba_hist_sqltext where sql_id = :sqlid
Note that only "highest consuming" statements are retained in AWR. So if the query you're interested in isn't the slowest, most executed, most disk access, etc. it may not be in the AWR data. You can get around this by "coloring" sql_ids of interest. This ensures they stay in AWR for as long as your retention period is set. You can do this by running:
exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(:sqlid);
If you just want an overview of your system in a time period (including "heaviest" SQL statements), the AWR report itself gives this. You can find an intro to this on oracle-base.
