JL Computer Consultancy

Using awk, cut, and paste to report changes in Oracle stats

Prior to Aug 1999


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.

Back to Main Index of Topics


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

Back to Main Index of Topics


#!/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}

Back to Main Index of Topics