Starting with Oracle 10g database management system, Oracle offers a set of scripts to extract performance statistics from Automatic Workload Repository (AWR) to generate a human readable report. This report will be a starting point in finding {and fixing them sometimes, almost with no additional effort} the bottlenecks in the database system.

Oracle automatically generates snapshots of the performance data once every hour and stores the statistics in the workload repository. While diagnosing some issues, it might be necessary to take several snapshots manually. create_snapshot procedure can be used to create a database snapshot manually, as shown below:

    % sqlplus / as sysdba
    SQL> exec dbms_workload_repository.create_snapshot();

Note down the date and time of all such snapshots; and use the corresponding snap IDs while generating the AWR report for a certain interval.

How to generate an AWR report?

Simply run the awrrpti.sql as shown below. The questions from the script are pretty straight forward to answer.

    sqlplus / as sysdba
    SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

How to interpret an AWR report?

Here are some useful resources (note that statspack interpretation is still applicable to AWR):

• Statspack 101: Interpreting Your Statspack Report by Troy Campano
  
• Interpreting the Statspack report at AskTom.com

For more information:

• Oracle® Database Performance Tuning Guide - Automatic Workload Repository
  
• Automatic Workload Repository (AWR) in Oracle Database 10g

__________
Technorati tags:
Oracle | Performance