AWR Reports
before implementing them on a production server.
Background
In Oracle 9 , we used the statspack reports to acquire statistics about
the database performance between two timed snapshots.
In Oracle10, the same principle applies, but the script names have changed
and have become slightly more complex.
You will find steps below which will help you set up a script system to
generate Automatic Workload Repository reports based on snapshots
taken at the same time daily, and then emailing them to yourself via the
Linux Mutt tool.
First script: /home/oracle/bin/spreport.sh
(make sure the file permissions are at least 744 ).
#!/bin/ksh
ORACLE_SID=dev1 ; export ORACLE_SID
export ORACLE_HOME=/home/oracle/product/10.2.0
export PATH=${PATH}:${ORACLE_HOME}/bin
sqlplus /nolog @/home/oracle/bin/sql/daily_spreport.sql > /dev/null 2>&1
# now you can mail the logs to yourself.
/usr/bin/mutt -s "AWR report 11h00 to 12h00" -a
/home/oracle/bin/log/spreport_11h00_to_12h00.html
yourname@yourISP.com < /home/oracle/bin/message1.txt
/usr/bin/mutt -s "AWR report 12h00 to 13h00 " -a
/home/oracle/bin/log/spreport_12h00_to_13h00.html
yourname@yourISP.com < /home/oracle/bin/message2.txt
Message1.txt and Message2.txt are just
plain text files which contain what will be in the body of your email.
message1.txt
Oracle stats report 11h00-12h00 attached
message2.txt
Oracle stats report 12h00-13h00 attached
daily_spreport.sql
Here you collect the snapid's which you will use
to base the reports on.
I have predefined the snapshots taken at 11h00,12h00 and 13h00.
(Note that tuning experts advise that to tune optimally, your snapshots
should be
no more than 15 minutes apart. )
connect sys/itspassword as sysdba
set termout off
variable snap1 number
variable snap2 number
variable snap3 number
begin
select
s.snap_id
into :snap1
from dba_hist_snapshot s
where trunc(cast(s.end_interval_time as date),'MI')
= trunc(trunc(sysdate)+11/24,'MI');
select
s.snap_id
into :snap2
from dba_hist_snapshot s
where trunc(cast(s.end_interval_time as date),'MI')
= trunc(trunc(sysdate)+12/24,'MI');
select
s.snap_id
into :snap3
from dba_hist_snapshot s
where trunc(cast(s.end_interval_time as date),'MI')
= trunc(trunc(sysdate)+13/24,'MI');
end;
/
@/home/oracle/bin/sql/run_spreport :snap1 :snap2
/home/oracle/bin/log/spreport_11h00_to_12h00.html
@/home/oracle/bin/sql/run_spreport :snap2 :snap3
/home/oracle/bin/log/spreport_12h00_to_13h00.html
exit
And finally:
You can get these from the following queries:
SQL> select instance_number,instance_name
from v$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------- 1 dev1 SQL> select dbid, name from v$database; DBID NAME ---------- --------- 1234567890 DEV1
run_spreport.sql
set termout off define begin_snap=&1 define end_snap=&2 define report_name=&3 define report_type = 'html'; define inst_num = 1; define num_days = 3; define inst_name = 'dev1'; define db_name = 'DEV1'; define dbid = 1234567890; @@?/rdbms/admin/awrrpti
Now you can schedule this with a cronjob to run daily at 14h30.
$ crontab -l 30 14 * * * /home/oracle/bin/spreport.sh >/dev/null 2>&1
