My First Experience Running SLOB – Don’t repeat my errors (AWR)

NOTE: Others SLOB related posts list avaiable from “My SLOB IO testing index”

If you are wondering what I am busy with then this post explains it.

As you may noticed I am still testing one of the Oracle systems using the SLOB framework and learning on my way. I ran several tests with the same parameters (Readers 24) and I noticed that for one reason or another awr.txt reports different runtimes:

[oracle@mega_host SLOB]$ grep mins awr*24* | grep Elapsed
awr_0w_24r.20120519_161251.txt:   Elapsed:               12.39 (mins)
awr_0w_24r.20120519_223935.txt:   Elapsed:               38.62 (mins)
awr_0w_24r.20120515_0143.txt:   Elapsed:               42.86 (mins)
awr_0w_24r.20120516_1316.txt:   Elapsed:               16.52 (mins)
awr_0w_24r.txt:   Elapsed:               41.94 (mins)
[oracle@mega_host SLOB]$

I was wondering why I am getting inconsistent runtime for kind of the same conditions. The answer to the mystery was very simple. The runit.sh script just generates awr.txt report for last and one before last awr snapshots.

[oracle@mega_host SLOB]$ tail -4 runit.sh
sqlplus -L '/as sysdba'  @awr/awr_snap > /dev/null
sqlplus -L '/as sysdba'  @awr/create_awr_report > /dev/null
[oracle@mega_host SLOB]$ cat awr/create_awr_report.sql
...
select
max(SNAP_ID)-1 begin_snap ,
max(SNAP_ID) end_snap
from dba_hist_snapshot;
...

The problem is that by default Oracle makes snapshots every hour. Therefore if your test process crossing an hour boundary awr.txt would not contain information for the whole SLOB test runtime.
To address the issue I have 2 possible solutions.
Solution 1 – IMHO the most reliable one – disable automatic AWR snapshots for the database:

[oracle@mega_host SLOB]$ cat off_awr_auto.sh
sqlplus "/ as sysdba"  <<EOF
set timing on
select * from DBA_HIST_WR_CONTROL;
begin
dbms_workload_repository.MODIFY_SNAPSHOT_SETTINGS(51120000, 51120000, 100, null);
end;
/
select * from DBA_HIST_WR_CONTROL;
EOF
[oracle@mega_host SLOB]$

NOTE: After a quick look on possible options my understanding is, if we set the interval to zero it disables BOTH automatic and manual snapshots. As we still would like to make manual snapshots we just set the AWR interval to very long value.

Solution 2 – Exclude AWR snapshots made at ’00′ minutes.

[oracle@mega_host SLOB]$ cat awr/create_awr_report.sql
...
select
max(SNAP_ID)-1 begin_snap ,
max(SNAP_ID) end_snap
from dba_hist_snapshot
where TO_CHAR( END_INTERVAL_TIME, 'MI' ) != '00';
...

NOTE: This option isn’t as reliable as the first one. However with assumption that SLOB test ins’t started/finished at t he beginning of an hours should work (if you like me generated some reports already this option could help you out to filter automatic snapshots).
Other modification that may safe you some time is to let runit.sh make a copy of awr.txt report for you including runtime parameter and a date as part of the report name.

[oracle@mega_host SLOB]$ tail -4 ./runit.sh
sqlplus -L '/as sysdba'  @awr/create_awr_report > /dev/null

mv ./awr.txt awr_${1}w_${2}r.`date +%Y%m%d_%H%M%S`.txt
date
[oracle@mega_host SLOB]$

I am still in the testing process however I think those few hints may help you to avoid some confusions I personally faced learning how to use SLOB IO testing framework from Kevin Closson.

Stay tuned for further updates,
Yury