Awr diff reports (awrddrpt.sql) are a great tool for comparing database performance characteristics from different time periods. Lot of times, the details in the report can point you to differences in workload profiles, or sql execution differences, helping you to narrow down problem areas.
Awr diff report, can compare the awr data for different time periods
– Period A v/s Period B for the same instance in the same database
– Period A in instance X v/s Period B in instance Y in the same database
– Period A in instance X in database P v/s Period B in instance Y in database Q
I routinely export awr data from different databases (awrextr.sql) and import them to a central database (awrload.sql). However one problem with this is that once it is in the central database, in 11gr2 there is no way of viewing this data via Enterprise Manager. So i end up writing sql statements to compare the data.
Below is a handy shell script that helps you compare two different time periods.
You can run the script using the syntax below.
./genawrd.sh dbid1 inst1 startsnapid1 endsnapid1 dbid2 inst2 startsnapid2 endsnapid2 credentials
eg:
./genawrd.sh 3401191500 3 1111 1112 1346415800 2 757 758 system/manager@rk01
#!/bin/bash
if [ $# != 9 ]
then
echo Syntax genawrd.sh dbid inst1 startsnapid1 endsnapid1 dbid2 inst2 startsnapid2 endsnapid2 credentials
exit 1
fi
l_dbid=$1
l_instid=$2
l_start_snapid=$3
l_end_snapid=$4
l_dbid2=$5
l_instid2=$6
l_start_snapid2=$7
l_end_snapid2=$8
l_credentials=$9
l_logdir='/u01/Temp/awrs'
l_awr_log_file=${l_logdir}/awrdrpt_${l_instid}_${l_instid2}_${l_start_snapid}_${l_end_snapid}_${l_start_snapid2}_${l_end_snapid2}.html
sqlplus -s $l_credentials << EOC > /dev/null
set head off
set pages 0
set lines 132
set echo off
set feedback off
set termout off
define inst_num=${l_instid};
define inst_num2=${l_instid2};
define dbid=${l_dbid};
define dbid2=${l_dbid2};
define report_type='html';
define report_name=${l_awr_log_file};
define begin_snap=${l_start_snapid};
define end_snap=${l_end_snapid};
define begin_snap2=${l_start_snapid2};
define end_snap2=${l_end_snapid2};
@$ORACLE_HOME/rdbms/admin/awrddrpi
EOC
