环境:(产品,平台,机型,软件版本,等) 与具体的环境无关
问题描述: 当我们在向数据库表装载数据或插入数据的时候，需要对其插入的效率进行实时监控，例如：当前对这张表的插入效率如何，XX笔/秒，对索引空间的使用情况如何，XX页/秒。通过对这些具体数据的实时监控，我们可以对插入数据的方式、方法进行适时调整，以获得最佳的插入效率 count_insert.sh

#!/bin/sh
##############################################################################

#
#
# Module: count_insert.sh
# Author: Richard ZHAN
# Description: Trace and calculate the inserting effectiveness by every partition
# For tables calculated by nubmer of rows
# For indices calculated by nubmer of used pages
#
# Change Log
#
# Date Name Description.................
# 25/02/2009 Richard ZHAN Start Program
#
###############################################################################

usage()
{
    printf "usage: count_insert.sh database tablename\n"
    exit 1
} 

# check usage; exit if incorrect. valid argument count is 2.
[ $# -ne 2 ] && usage

AWK=""
if [ -z "$AWK" ]
then for awker in gawk nawk awk
    do
    for dir in `echo $PATH | sed 's/:/ /g'`
        do
        if [ -x "$dir/$awker" ]
            then AWK="$dir/$awker"
            break 2
        fi
        done
    done
fi

################################################################################
# 
#
# MAIN BODY OF SCRIPT 
#
# 
#
################################################################################

#Initialize environment
cnt=0
count_insert="count_insert.txt"
tmp_count_insert="${count_insert}.$$"
result_count_insert="result_${count_insert}"

rm -f ${count_insert}
rm -f ${tmp_count_insert}
#rm -f ${result_count_insert}

while [ ${cnt} -lt 2000 ]
do
    date=`date "+%H%M%S"`
    # seconds=`expr ${date} / 10000 \* 3600 + ${date} / 100 % 100 \* 60 + ${date} % 100`
    (onstat -d;oncheck -pt $1:$2) | ${AWK} -v date=${date} -v count_insert=${count_insert} -v tmp_count_insert=${tmp_count_insert} '
    BEGIN {flag=0;Dbspaces_flag=0;}
    #For dbspace
    /^Dbspaces/ {Dbspaces_flag=1;}
    Dbspaces_flag == 1 {
        if ( $2 ~ /[0-9]+/ ) {
            Dbspaces[$2] = $NF;
        } else if ( $0 ~ /maximum$/ ) {
            Dbspaces_flag = 0;
        }
    }

    #For table
    /^TBLspace Report for/ || /Table fragment.*in/ {
        if ( $0 ~ /^TBLspace Report for/ ) {
            tablename=$4
            flag = 1; #For nonfragmented table
        } else {
            printf "%s %s %s ", date, tablename, $5 >>     count_insert; #For IDS 9.4x
            printf "%s %s %s ", date, tablename, $5 >>     tmp_count_insert; #For IDS 9.4x
            # printf "%s %s %s ", date, tablename, $7 >> count_insert; #For IDS 10.0x
            # printf "%s %s %s ", date, tablename, $7 >> tmp_count_insert; #For IDS 10.0x
            flag = 2; #For fragmented table
        }
    }

    #For index
    /Index.*fragment.*in/ {
        printf "%s %s %s ", date, $2, $6 >> count_insert; #For     IDS 9.4x
        printf "%s %s %s ", date, $2, $6 >> tmp_count_insert; #For IDS 9.4x
        # printf "%s %s %s ", date, $2, $8 >> count_insert; #For IDS 10.0x
        # printf "%s %s %s ", date, $2, $8 >> tmp_count_insert; #For IDS 10.0x
        flag = 3;
    }

    /Number of rows/ && (flag == 1 || flag == 2) {
        #For nonfragmented table
        if ( flag == 1 ) {
            nrows = $4;
            #For fragmented table
        } else { 
            printf "%d\n", $4 >> count_insert; 
            printf "%d\n", $4 >> tmp_count_insert; 
            flag = 0;
        }
    }

    #For index
    /Number of pages used/ && flag == 3 {
        printf "%d\n", $5 >> count_insert; 
        printf "%d\n", $5 >> tmp_count_insert; 
        flag = 0;
    }

    #For nonfragmented table
    /Partition partnum/ && flag == 1 {
        printf "%s %s %s %d\n", date, tablename, Dbspaces[int($3/1048576)], nrows >> count_insert;
        printf "%s %s %s %d\n", date, tablename, Dbspaces[int($3/1048576)], nrows >> tmp_count_insert;
        flag = 0;
    }'
    ${AWK} 'BEGIN {date=0;}
    {
        if ( date == 0 ) {
            date = $1;
            second = int((date / 10000) % 100) * 3600 + int((date / 100) % 100) * 60 + date % 100;
        }
        if ( NF == 4 && date != $1 ) {
            second1 = int(($1 / 10000) % 100) * 3600 + int(($1 / 100) % 100) * 60 + $1 % 100;
            # printf "%s %s %s %d %d %d %d %d\n", $1, $2, $3, ($4 - array[$2,$3]) / (second1 - second), $4, array[$2,$3], second1, second;
            printf "%s %s %s %d\n", $1, $2, $3, ($4 - array[$2,$3]) / (second1 - second);
        } else {
            array[$2,$3] = $4;
        }
    }' ${count_insert} >> ${result_count_insert} 
    mv ${tmp_count_insert} ${count_insert}
    sleep 1
    cnt=`expr ${cnt} + 1`
done