AWR ADDM and ASH Report Generation in 11g | Oracle Performance Tuning

AWR ADDM and ASH Report Generation in 11g | Oracle Performance Tuning

1) AWR Report:

Before creating any single report, make sure snapshot capturing is enable on your database. If not, you can run the below mentioned command to create one:

exec dbms_workload_repository.create_snapshot;

The Report will be generated on the basis of the snapshots taken. Take 4-5 snapshots or run the above mentioned command to create snapshots each in 5 minutes duration at least. Run the below mentioned query to check the snapshots created in your database,

SQL> select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin from dba_hist_snapshot order by 1;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       465          1 19/07/17 18:30:50
       466          1 19/07/17 18:41:57
       467          1 19/07/17 19:07:33
       468          1 19/07/17 19:08:30

Now find the below mentioned guidelines to check the creation of AWR Report,

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

You can create snap using the below

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Below query help to find snaps information

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

2) ADDM Report:

Below mentioned is the script run to generate ADDM Report

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

3) ASH Report:

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql