airydbablog 6:25 pm on June 4, 2016
Tags:   

Introduction of Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR) in Oracle 11g :

Oracle has provided many performance gathering and reporting tools time to time. Before oracle 8i UTLBSTAT / UTLESTAT scripts were used to monitor performance metrics information. In Oracle8i the STATSPACK functionality was introduced which was extended in Oracle9i. In Oracle 10g STATSPACK has evolved into the Automatic Workload Repository (AWR), which was made more comprehensive in Oracle 11g.

 STATSPACK :

STATSPACK was introduced in Oracle8i (8.1.6), which was the replacement for the UTLBSTAT/UTLESTAT scripts. Along with additional reporting, STATSPACK can store snapshots of system statistics over time, allowing greater accuracy and flexibility. Detailed Information about the installation and usage of STATSPACK can be found in the document $ORACLE_HOME/rdbms/admin/spdoc.txt

$ORACLE_HOME/rdbms/admin/catdbsyn.sql;

$ORACLE_HOME/rdbms/admin/dbmspool.sql;

On running $ORACLE_HOME/rdbms/admin/spcreate.sql script as SYS, STATSPACK package and PERFSTAT user along with the necessary schema objects created.

In SQL prompt after connecting with PERFSTAT user, and executing  “ SQL>EXEC STATSPACK.snap; ” we can take a snapshot of the system statistics

For analysis purpose you can take another snapshot and which will provide you a fix start and end point of your analysis. You can take multiple snapshots and use any for your start and end point, which is the advantage over UTLBSTAT / UTLESTAT means in STATSPACK there is no set start or end point.

The collection of system snapshots can be automated with the DBMS_JOB package. The $ORACLE_HOME/rdbms/admin/spauto.sql  script can be used to schedule system snapshot collections on every 1 hour.

If some time passed and if you have the collection of snapshots the You can get a snapshot report on the basis of snapshot range with the help of following script $ORACLE_HOME/rdbms/admin/spreport.sql and you can see what was the performance in such a such time.

If you are automating snapshot collection you will need to delete snapshots from time to time. This can be done by running the $ORACLE_HOME/rdbms/admin/sppurge.sql file as the PERFSTAT user. This script deletes a range of snapshots by prompting for the start and end points.

If you want to drop all the environment of STATPACK then we can do this by running scrip $ORACLE_HOME/rdbms/admin/sppurge.sql

Once you have at least two snapshots you can run the STATSPACK report and find out the change in the statistics over the analysis period. We will run $ORACLE_HOME/rdbms/admin/spreport.sql script which will prompt you for the start and end snapshots along with a filename for the output report.

Automatic Workload Repository (AWR) :

 AWR is a replacement of STATSPACK and is a performance baseline/ tuning feature built in ORACLE 11g.

  1. By default AWR gathers statistics in every 60 Minutes ( 1 Hrs ).
  2. AWR data tables stored in SYSMAN schema and in SYSAUX tablespace.
  3. Statistics (SNAPSHOT) retained for 8 days. Both SNAPSHOT taken time and retained time is modifiable.
  4. Data Collection Level are : Basic , Typical ( Default ) and All.

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.

The repository is a source of information for several other Oracle 11g features including:

  • Automatic Database Diagnostic Monitor (ADDM)
  • SQL Tuning Advisor
  • Segment Advisor
  • Undo Advisor

Thank you for reading …..This is Airy…Enjoy 🙂

 

#awr