AWR compare report

I came across this while doing some dbreplays, and found it very useful.

First, lets say you have a RAC cluster,  and you want to do some performance comparisons .. What's one of the issues you run into ?  For me it is trying to figure out which nodes I care about, and running the AWR report for that node. This is exasperated with a Full Rack Exadata.  8 nodes to compare.  Well this is what I use to compare 2 time periods across all nodes.  I also increase some of the reporting thresholds..

First the script to gather the report. (here)

To get this to work change the following

dbid  - dbid for the first time period
begin_snap - begin snap first time period
end_snap - end snap first time period

dbid2 - dbid for the second time period
begin_snap2 - begin snap second time period
end_snap2 - end snap second time period

Also notice that I changed top_n_** values to give me more data

Rem    NAME
Rem      awr_full.sql - Workload Repository Global Compare Periods Report
Rem
Rem    DESCRIPTION
Rem      RAC Version of Compare Period Report
Rem
Rem
Rem    NOTES
Rem      Run as SYSDBA.  Generally this script should be invoked by awrgdrpt,
Rem      unless you want to pick a database and/or specific instances
Rem      other than the default.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      without executing the script through awrgdrpt, then
Rem      do something similar to the following:
Rem
      define  num_days     = 0;
      define  dbid         =2415508472; 
      define  instance_numbers_or_ALL    = 'ALL';
      define  begin_snap   = 35727;
      define  end_snap     = 35728;
      define  num_days2    = 0;
      define  dbid2        = 2415508472;
      define  instance_numbers_or_ALL2    = 'ALL';
      define  begin_snap2  = 35728;
      define  end_snap2    = 35729; 
      define  report_type  = 'html';
      define  report_name  = /tmp/awr_report.html
      define top_n_files        = 50;
      define top_n_segments     = 50;
      define top_n_services     = 50;
      define top_n_sql          = 100;
      @@?/rdbms/admin/awrgdrpi
!./mail_full.sql
exit

The second to last line of the script is to mail the report, and the script is here.

echo "From: replay_report@oracle.com"  > /tmp/mailfilebsg
echo "To: raddba@oracle.com"   >> /tmp/mailfilebsg
echo "Subject: DBREPLAY output "   >> /tmp/mailfilebsg
echo "Mime-Version: 1.0"      >> /tmp/mailfilebsg
echo 'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"'   >> /tmp/mailfilebsg
echo "--DMW.Boundary.605592468" >> /tmp/mailfilebsg
echo " " >> /tmp/mailfilebsg
echo " dbreplay output " >> /tmp/mailfilebsg
echo " " >> /tmp/mailfilebsg
echo "--DMW.Boundary.605592468" >> /tmp/mailfilebsg
echo 'Content-Disposition: inline; filename="dbreplay.html"' >> /tmp/mailfilebsg
echo "Content-Transfer-Encoding: 7bit" >> /tmp/mailfilebsg
cat /tmp/awr_report.html >> /tmp/mailfilebsg
echo "--DMW.Boundary.605592468" >> /tmp/mailfilebsg
/usr/sbin/sendmail bryan..grenn@oracle.com < /tmp/mailfilebsg

The second script will mail you the output as an attachement.  So when using it, be sure to make the E-mail address yours, and change the subject, and filename to be what you want.

Enjoy.