Jeff Taylor's Weblog
-
Sun
January 24, 2014
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.
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.
Here is a script "run.sh" that controls the gathering of data for the benchmark execution environment:
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.
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 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.
Join the discussion
Comments ( 1 )
What about ADDM and ASH reports also?