SSTS Blog

Some news and tidbits that we share

Querying the Oracle AWR tables

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Jul 11 in Blog 0 Comments

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

Tags: Untagged
Hits: 64987

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments