Systems Engineering and RDBMS

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”
FROM DBA_HIST_SNAPSHOT
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.

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
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
FROM
TABLE(dbms_workload_repository.awr_report_text
(1481523096,1,3504,3505)
);

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.

Resources:

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

One Response to “Creating an Adhoc AWR (Automatic workload repository) Report”

  1. an oracle dba said

    be lazy🙂, may use
    @?/rdbms/admin/awr* oracle db shipped scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: