migrated from stackoverflow.com Mar 3 '13 at 14:35
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:
This view holds various *_elapsed columns, which state the increase in the given snap (time period). e.g.:
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:
The sql_id is a hash of the text of the SQL itself. You can find the actual statement from:
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:
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 .