Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for June 17th, 2009

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.


———- ———- ———-
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.

SELECT output

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.


  • Oracle-base article – here.
  • Oracle 10g Performance Tuning guide – here.

Posted in Oracle | 1 Comment »