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

Posted in: Technical Track

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

Interested in working with Yury? Schedule a tech call.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

3 Comments. Leave new

My SLOB IO testing index | The Pythian Blog
May 20, 2012 8:37 pm

[…] 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)“ […]

Reply

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

Reply

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? :)

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *