My First Experience Running SLOB – Don’t Repeat My Errors (AWR)

NOTE: There is a list of SLOB-related posts available in “My SLOB IO testing index“.

If you are wondering what I have been busying myself with, this post explains it.

As you may have 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 noticed that for one reason or another awr.txt reports different runtimes:

[[email protected]_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)
[[email protected]_host SLOB]$

I was wondering why I was getting inconsistent runtimes for similar conditions. The answer to the mystery was very simple. The runit.sh script just generates awr.txt reports for the last and before last awr snapshots.

[[email protected]_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
[[email protected]_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 Oracle makes snapshots hourly by default. Therefore, if your test process crosses the one 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:

[[email protected]_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
[[email protected]_host SLOB]$

NOTE: After a quick look at possible options, my understanding is that 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 a very long value.

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

[[email protected]_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 the assumption that SLOB tests aren’t started/finished at the beginning of an hour, it should work. (If you, like me, generated some reports already, this option could help you filter automatic snapshots.)
Another modification that may save you some time is letting runit.sh make a copy of awr.txt reports for you, including runtime parameter and a date as part of the report name.

[[email protected]_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
[[email protected]_host SLOB]$

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

Stay tuned for further updates,
Yury