Tag Archives: shell script

Shell script to generate awr diff reports

Leave a reply

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.

?View Code BASH
./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
?View Code BASH
#!/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

Shell script to generate awr reports for a range of snapshots

Leave a reply

We have been doing some performance testing in the recent days. We were running some workload using an application, capturing awr snapshots every 15 minutes on a 8 node rac cluster. I needed to generate the awr reports between each of the snapshots for each of the nodes. Running awrrpt.sql for each 15 minute intervals for each one of the 8 nodes would have been a tedious task. So i wrote a bash shell script that generates those awr reports.

It takes the database id, instance number, start snapshot id and end snapshot id as arguments (you can query dba_hist_snapshot to find out the start and end snapshot id’s you want to use). The directory in which it generates the reports is hardcoded in the script. The script has to be run as the oracle user  and it logs in as sys to generate the reports (You can modify as you need if you want it to be run as a different user). I have tested it only on Linux.

You can run it as ./genawrs.sh dbid  instancenumber beginsnap endsnap, to generate the reports.  This means that you have to run it once for each instance you need the report for. The script can be found below.

#!/bin/bash

if [ $# != 4 ]
then
echo “Syntax  genawrs.sh dbid instanceId startsnapid endsnapid”
exit 1
fi

l_dbid=$1
l_instid=$2
l_start_snapid=$3
let l_end_snapid=$4-1

# For all snapshot id’s
# Set the next snapshot id as current snapshot id + 1
# Spool a log file
# Log into sqlplus and call dbms_workload_repository.awr_report_text
# To generate the awr

for i in `seq $l_start_snapid $l_end_snapid`
do
let l_next_snapid=$i+1;
l_awr_log_file=”/awrs/out/awrrpt_${2}_${i}_${l_next_snapid}.log”

sqlplus -s / as sysdba << EOC
set head off
set pages 0
set lines 132
set echo off
set feedback off
spool $l_awr_log_file
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_text
($l_dbid,$l_instid,$i,$l_next_snapid)
);
spool off
EOC
done