Gathering Database Statistics in a Test Harness

Use of the Automatic Workload Repository (AWR) is fundamental to understanding Oracle Database performance. By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. You can also manually create snapshots, but this is usually not necessary.

When benchmarking, it is helpful to be able to create an AWR report that covers the exact interval of benchmark execution. Since my benchmarks don't begin or end at the top of the hour, I take an AWR snapshot immediately before and after benchmark execution. At benchmark completion, an AWR report is generated that exactly covers the benchmark execution interval.

Here is a simple shell script "snap.sh" that takes a new snapshot and returns the ID of the snapshot. I call this script before and after benchmark execution.

#!/bin/bash

result=$(sqlplus -S sys/SysPass as sysdba << EOF

set head off
set term off
set echo off
set trim on
set trimspool on
set feedback off
set pagesize 0
exec sys.dbms_workload_repository.create_snapshot;
select max(snap_id) from dba_hist_snapshot;
EOF
)

echo $result


Here is a script "gen_awr.sh". I call this script after benchmark execution with my preferred name for the report, and the before and after snap IDs.

sqlplus / as sysdba << @EOF
@$ORACLE_HOME/rdbms/admin/awrrpt
html
1
$2
$3
$1
exit
@EOF

Here is a script "run.sh" that controls the gathering of data for the benchmark execution environment:

Comments:
run.sh

#!/usr/bin/env bash

###
# Usage
###
[ $# -eq 0 ] && { echo "Usage: $0 <TestSize>"; exit 1; }

###
# Set up
###
size=$1
export DateStamp=`date +%y%m%d_%H%M%S`
export DIR=/STATS/`hostname`_${size}_$DateStamp
mkdir -p $DIR

###
# Start gathering statistics
###
vmstat 1 > $DIR/vmstat.out &
vmstat_pid=$!
Take an AWR snapshot before execution
first=`./snap.sh`

###
# Run the benchmark
###
time sqlplus User/UserPass @RunAll $size > $DIR/std.out 2> $DIR/std.err

###
# Stop gathering statistics
###
Take an AWR snapshot after execution last=`./snap.sh`

kill $vmstat_pid

###
# Generate the report
###
Generate the AWR report
./gen_awr.sh $size $first $last > /dev/null
mv $size.lst $DIR/awr_$size.html


###
# View the AWR report
###
firefox $DIR/awr_$size.html

After many benchmark runs, I have one directory per benchmark execution, named <nodename>_<size>_<date>_<time>, and each directory contains one AWR report that exactly covers the benchmark interval.


Comments:

What about ADDM and ASH reports also?

Posted by guest on February 10, 2014 at 09:43 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

user12620111

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today