AWR snashots are a useful way to gain insight into what was going on in the database in recent history. Viewing snapshot reports can supply very detailed metrics for a specific interval (default is hourly). One limitation here is when you would like to analyze a specific metric over multiple snapshots. A way to accomplish this type analysis is to query the AWR tables directly. In my case I wanted to review session logical reads over all snapshots to get a feel for overall workload of the database over time. One thing to be aware of is that some AWR values are cumulative and so must be compared to the previous snapshot in order to see what accumulated over the interval. Using analytic functions here allows you to do this easily. See the example below and along with a link for more details on the AWR tables.

select s.END_INTERVAL_TIME, value-lag(value) over(partition by stat_name order by s.BEGIN_INTERVAL_TIME) session_logical_reads
from DBA_HIST_SNAPSHOT s

 

join dba_hist_sysstat ss on s.snap_id = ss.snap_id
where ss.stat_name like '%session logical reads%'

http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html#script-find-expensive