I have always been unenthusiastic about storing statistical snapshots inside the Oracle database - a prejudice perhaps dating back to the days when the cost of collecting the stats would be almost as large as the basic cost of running the database. Instead I have usually dumped stats to (very) flat files, and then used simple shell utilities to find the differences between two sets of stats.
With the appearance of PL/SQL, utl_file and dbms_output I have produced platform-independent variations on this theme, (awk seems to vary slightly, but painfully, from platform to platform - you may need to use nawk instead) but here is a sample of how I first took performance snapshots over time about 12 years ago.
This note uses a report on the v$filestat (Version 7) view as a basis for demonstrating how Unix text handling tools can be used relatively easily to calculate and report the changes between two snapshots taken at different times. A sample snapshot appears below, the script that generated it is on a separate page.
The nice feature of an approach like this is that you can kick off a simple stats report every 15 minutes or so during the day (perhaps identifying the output file by a terminating time stamp), and then find the difference between any pair of output files. Clearly an improvement over the old bstat/estat pair.
Sample of input (and output format)
File Reads Blks_Rd Avg_Time Writes Blks_wrt Avg_Time
----- -------- -------- -------- -------- -------- --------
1 1774679 1774679 .759 22935 22935 4.411
2 8654 25678 1.073 6498 25764 2.546
3 15206 41355 1.135 12364 43546 2.451
4 3181 3181 1.027 6703 6703 5.543
5 2380 2380 .932 1530 1530 5.497
6 9454 23854 1.470 9063 30820 2.599
7 236 236 2.051 0 0 .000
8 0 0 .000 0 0 .000
11 32797 91000 1.187 25954 94469 2.539
12 15256 43716 1.537 14374 46862 2.503
13 18742 18742 1.305 0 0 .000
#!/bin/ksh # # Script: filestat.sh # Author: Jonathan Lewis # Dated: The dim and distant past # Purpose: Read two files of the format filestat.XXX # and find the difference between them # # Usage: # filestat.sh xxxx yyyy # filestat.sh 041215 041230 # # produces filestat.041230.diff # # Notes: # Set total length of common text columns in m_text_end # Scripts writes to /tmp #
m_file_name=`basename $0 .sh`
typeset -i m_text_end m_text_end=5
m_file1=$1 m_file2=$2 m_process=$$
# # Create filename for: # Leading text of each line # Value columns from first file # Value columns from second file # Concatenated values file # Processed output from concatenated values file #
m_titles=temp_${m_file_name}.${m_process}
m_value1=temp_${m_file_name}.${m_process}_1
m_value2=temp_${m_file_name}.${m_process}_2
m_values=temp_${m_file_name}.${m_process}_v
m_values2=temp_${m_file_name}.${m_process}_v2
# # Cut leading text off and save #
cut -c1-${m_text_end} ${m_file_name}.${m_file1} >/tmp/${m_titles}
# # Cut trailing values off each file and save #
m_text_end=m_text_end+1
cut -c${m_text_end}- ${m_file_name}.${m_file1} >/tmp/${m_value1}
cut -c${m_text_end}- ${m_file_name}.${m_file2} >/tmp/${m_value2}
# # Stick two sets of values together #
paste -d" " /tmp/${m_value1} /tmp/${m_value2} >/tmp/${m_values}
# # Copy out title lines from values section, then print out # the required arithmetic work on the two sets of values # typically this will be: # Column X+N minus column X # where N is the total number of values in the main output # # In this case the 'average time'columns require a little extra work # Note the 'a?b:c' syntax to avoid the divide by zero error. # # Might need to change awk to nawk !! #
awk ' { if (NR <= 3)
printf ("%8s %8s %8s %8s %8s %8s\n", \
$1, $2, $3, $4, $5, $6)
else {
m_read_time = $9 * $7 - $3 * $1
m_write_time = $12 * $10 - $6 * $4
printf ("%8li %8li %8.3f %8li %8li %8.3f\n", \
$7-$1, $8-$2, \
($7 - $1 == 0) ? 0.0 : (m_read_time / ($7 - $1)), \
$10-$4, $11-$5, \
($10 - $4 == 0) ? 0.0 : (m_write_time / ($10 - $4)) \
)
}
}' </tmp/${m_values} > /tmp/${m_values2}
# # Attach the results back to the original text lead-in #
paste -d" " /tmp/${m_titles} /tmp/${m_values2} >${m_file_name}.${m_file2}.diff
# # Get rid of the temporary files #
rm /tmp/${m_titles}
rm /tmp/${m_value1}
rm /tmp/${m_value2}
rm /tmp/${m_values}
rm /tmp/${m_values2}