Creating an Adhoc AWR (Automatic workload repository) Report
Posted by decipherinfosys on June 17, 2009
In Oracle 10g, Oracle replaced statspack with AWR to collect the performance data and reporting it in a much easier laid out fashion. AWR provides very rich functionality for performance statistics including wait events, resource intensive SQLs and other related information. In this blog post, we will cover how we can generate the latest AWR report for troubleshooting.
By default, Oracles takes snapshot of the data every hour and keeps it for 7 days. So in order to generate desired reports, we can go back up to 7 days. Snapshot information is stored in DBA_HIST_SNAPSHOT view. Let us first get the latest snap_id to get the AWR report for latest time interval. Snap_id is unique id for each time interval specified to collect the data.
SELECT dBID,(MAX(SNAP_ID)-1) “START_SNAP”, MAX(SNAP_ID) ” END_SNAP”
GROUP BY DBID;
DBID START_SNAP END_SNAP
———- ———- ———-
1481523096 3504 3505
Once we get the value for db_id and range of snap_ids for latest time interval, we can use it in following sql to generate the report. One has to make sure that user has an execution privilege on dbms_workload_repository package. awr_report_text procedure takes five parameters. For first four parameters we have to pass the value and for the fifth one we can keep default value. Here is the definition.
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
Value for l_Dbid, l_bid and l_eid is obtained from the first sql. Value for instance_id we can get it using sys_context function. Usually it is 1 if it is non-rac environment. Here is the sql.
We can spool the output to the file from above sql and open it up as a text file to investigate the results. AWR is very wide topic. This is just to show how we can get the adhoc AWR reports for a given range of snap_ids or time interval. Oracle-base has very informative article on this and how to maintain snapshots as well (Dropping/Creating snapshots), how to modify snapshot settings to increase or decrease the time interval etc.
Most companies has proper procedures in place to generate the AWR report and store it or ftp it at common location which can be accessed by DBA and/or application development team for later diagnosis.