How to use AWR and ADDM - very short explanation

In the last post about setting up stress tests on Oracle databases I recommended AWR and ADDM to see the results of your tests. These tools are unique gems of programming. For those who are not familiar with them, here is a very short, but hopefully efficient instruction, how to handle it manually.


Automatic Workload Repository AWR
For details see the documentation. What you need to know here: per default AWR takes snapshots every 60 minutes and you can create simple AWR-Reports by typing at your SQL prompt
SQL> @?/rdbms/admin/awrrpt.sql

and the rest is self explanatory. You need system / sysdba / dba - privileges.

In addition you can take snapshots whenever you like. So before the test run at the SQL prompt
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

Do your test and take another snapshot, because we always need a snapshot interval, a begin and an end snapshot_id.

Automatic Database Diagnostic Monitor ADDM
After every scheduled AWR snapshot the ADDM is automatically started and you can find the results in the advisor findings, just by typing
SQL> SELECT DBMS_ADDM.GET_REPORT('<taskname>') from dual;

And here is the complete script how you can do it quickly, efficiently.

Create a SQL script ADDM.sql e.g.

REM HWK 2011
set serveroutput on
prompt ==========================
prompt Execute an ADDM Analysis over a snapshot interval
prompt
prompt list of available snap_ids
accept daysback number prompt 'How may days back: '
set pages 0
set verify off
declare
cursor c_snaps is
  select snap_id, to_char(end_interval_time, 'DD.MM.YY HH24:MI') endtime
    from dba_hist_snapshot
    where instance_number = 1    /* On a RAC you can choose the instance_number of interest */
    and end_interval_time >= trunc(sysdate - &daysback)
    order by snap_id;
v_snaps c_snaps%rowtype;

begin
 open c_snaps;
loop
 fetch c_snaps into v_snaps;
 exit when c_snaps%notfound;
 dbms_output.put_line('    ' || v_snaps.snap_id || '    ' || v_snaps.endtime);
end loop;
  close c_snaps;
end;
/

accept beginsnap prompt 'Enter begin snapshot: '
accept endsnap prompt 'Enter end snapshot: '
accept taskname prompt 'Enter name of the Task: '
Pause Continue or press CtlC
------------

VAR tname VARCHAR2(30);

begin
:tname := '&taskname';
DBMS_ADDM.ANALYZE_DB(:tname, &beginsnap, &endsnap);
end;
/
set echo off
SET LONG 1000000 longchunksize 200 lines 200 PAGESIZE 0 trimspool on;
spool &taskname..log
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
spool off

Run the script at the SQL prompt simply typing
SQL> @ADDM

The extension *.sql is not required. Then go and study the <taskname>.log in your working directory.

And that is all. You should study the reports thoroughly. Not every recommendation is "God's word", but take it serious, because this comes from a piece of artificial intelligence.