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

May 19, 2012 / By Yury Velikanov

Tags: , , ,

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:

[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 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.

[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 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:

[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 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.

[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 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.

[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 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

3 Responses to “My First Experience Running SLOB – Don’t Repeat My Errors (AWR)”

  • [...] Run SLOB several times and make sure that the results you are getting are consistent. Otherwise question those. One thing that I found during my tests was the fact that earlier versions of SLOB had a “bug” generating awr.txt report. AWR didn’t cover all my tests’ time. For details and solution see “My First Experience Running SLOB – Don’t repeat my errors (AWR)“ [...]

  • Fairlie Rego says:

    Very good tip!… Realised this only after I read this blog item

    • Yury says:

      Thanks Fairlie for the feedback. I hope you do enjoy SLOB experience the same way I did :). I do have more hints listed under the SLOB Index blog post referenced at the beginning of this post.

      PS SLOB king of remaining the Oracle LIO test I have passed to you few years ago, isn’t it? :)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>