JL Computer Consultancy

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}