Performance analysis reports in Oracle

Sometimes you wonder why your database is slow or you just want to know what those oompa-loompas inside the Oracle black box are up to. One of the tools in your investigation can be the so-called Automatic Workload Repository, which contains an overload of information about what is going on in your database.

Some information from AWR can be seen inside the Enterprise manager console, but for serious analysis you will want to generate an AWR report in plaintext or more probabaly HTML format.

The report can be generated by running a SQL*Plus script. Logon to your database server as usual and run a following command:


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


The script will prompt you for your choices about how you want your report.

1. First, you must choose an output format. You probably will want a html version, but sometimes Oracle support team will ask you for a plaintext version.
2. You must enter a database ID. You most likely have only one database and the available choices are listed, so just copy the ten digit DB ID here.
3. If you run more than one instance, enter the number of the instance you want your report from. Unfortunately, you cannot generate a cumulative report for all your instances in RAC environment, so just pick one.
4. The script asks you, how far back in days you want to look for AWR snapshots. If you want just the recent activity, enter 1.
5. The script lists all available snapshots for the number of days specified in the previous step. If you entered 1, it will list just today's snapshots. Most commonly, the snapshot is done every hour. First, you must select a beginning snapshot and in the second step you select the ending snapshot. If you want the report for previous whole hour, enter the last two snapshot numbers, for example 2873 and 2874.
6. At last, you get prompted for a file name. The default is probably OK for most people, so just hit enter and the system gets to work on your report.

The generated report HTML file is located in the directory you ran the script from. Transfer the file to your computer or somewhere, where you can access it and open it in your favorite browser.

Your developers will most likely be interested in the section reporting the most frequently and most costly SQL statements. Your DBA will find interesting information about I/O activity, memory advisory tables etc. etc.

In a future article, I will try to point out some interesting sections in this report. Until then, self-study time.