A little thought for those who use OEM to generate AWR reports – if you generate the standard HTML form and then try posting it into (say) the OTN database forum when you want some help interpreting it, it won’t look very readable (even if you do remember to put the right tags before and after to reproduce it in fixed font).
So it’s a good idea to know how to produce the text format – which is what I do most of the time (especially since I often end up with a simple telnet or putty session into a client server). Take a look at $ORACLEHOME/rdbms/admin for all the scripts starting with “awr” – there’s quite a lot of them, and the number keeps growing. Apart from finding a script that will give you the standard AWR report in a good old-fashioned text format, you may that newer versions of Oracle include a few useful variations on the theme.
Here’s a list from the 220.127.116.11 home ($ORACLE_HOME/rdbms/admin) with (mostly) the one-line description from the start of file. I’ve put the most useful ones in the first list:
awrrpt.sql -- basic AWR report awrsqrpt.sql -- Standard SQL statement Report awrddrpt.sql -- Period diff on current instance awrrpti.sql -- Workload Repository Report Instance (RAC) awrgrpt.sql -- AWR Global Report (RAC) awrgdrpt.sql -- AWR Global Diff Report (RAC) awrinfo.sql -- Script to output general AWR information
For most people the awrrpt.sql and awrsqrpt.sql are likely to be sufficient, but the “difference between two periods” can be very useful – especially if you do things like regularly forcing an extra snapshot at the start and end of the overnight batch so that you can (when necessary) find the most significant differences in behaviour between the batch runs on two different nights.
If you get into the ‘RAC difference report’ you’ll need a very wide page – and very good eyesight !
There are also a lot of “infrastructure and support” bits – some of the “input” files give you some nice ideas about how you can write your own code to do little jobs like: “run the most recent AWR report automatically”:
awrblmig.sql -- AWR Baseline Migrate awrload.sql -- AWR LOAD: load awr from dump file awrextr.sql -- AWR Extract awrddinp.sql -- Get inputs for diff report awrddrpi.sql -- Workload Repository Compare Periods Report awrgdinp.sql -- Get inputs for global diff reports awrgdrpi.sql -- Workload Repository Global Compare Periods Report awrginp.sql -- AWR Global Input awrgrpti.sql -- Workload Repository RAC (Global) Report awrinpnm.sql -- AWR INput NaMe awrinput.sql -- Get inputs for AWR report awrsqrpi.sql -- Workload Repository SQL Report Instance
I usually carry copies of the scripts with me when I’m troubleshooting in case I need them at client sites – sometimes I’m not allowed the privileges I really need to do rapid troubleshooting, but if I can persuade the DBA to give me execute privileges on package dbms_workload_repository and select privileges on a couple of the necessary tables and views then I can run the reports from an otherwise “minimal” account.
There are also a couple of deceptively named files that you might miss in 11.2:
spawrrac.sql -- Server Performance AWR RAC report spawrio.sql -- AWR IO Intensity Report spadvrpt.sql -- Streams Performance ADVisor RePorT
Although the initial letters in the names suggest that these files might fall in with statspack, they actually report from the AWR tables – however the first one (spawrrac.sql) was only a temporary measure, and prints out the warning message:
This script will be deprecated. The official release of the Global AWR report is awrgrpt.sql
So if you’re using the spawrrac.sql – stop it.