#!/bin/ksh #============================================================================ # File: iostat.sh # Date: 08nov99 # Author: Tim Gorman, Evergreen Database Technologies, Inc. # # Description: UNIX shell script to monitor I/O utilization using the # UNIX utility "iostat". # # The script calls "iostat -xnP 60 2" once per minute to gather # minute-by-minute statistics. It logs into the current Oracle # database using the Oracle "username/password@connect-string" # stored in the shell variable "_UnPwd" (see below), creates # a table for storing the "iostat" information, and then uses # SQL to produce a report to the output file named # "iostat_.lst". # # Example: # Calling this script as follows: # # $ iostat.sh 35 # # will produce a report based on thirty-five 1-minute samples # of I/O activity using "iostat -xnP 60 2". This information, # stored in detail in the Oracle database in a table named # TEMP_IOSTAT, will be summarized in the output file # "iostat_.lst"... # # Modifications: # TGorman 08nov99 written # #============================================================================ # #---------------------------------------------------------------------------- # Oracle account username, password (and optional TNS "connect-string") to # connect into SQL*Plus and SQL*Loader; must have (at least) permissions for # CREATE SESSION and CREATE TABLE... #---------------------------------------------------------------------------- _UnPwd=scott/tiger # #---------------------------------------------------------------------------- # ...validate command-line parameters... #---------------------------------------------------------------------------- if (( $# != 1 )) then echo "Usage: iostat.sh ; aborting..." echo "\t\"\" must be between 1 and 1440." exit 1 fi # #---------------------------------------------------------------------------- # Validate the value provided... #---------------------------------------------------------------------------- integer _Mins=${1} if (( ${_Mins} < 1 || ${_Mins} > 1440 )) then echo "Usage: iostat.sh ; aborting..." echo "\t\"\" must be between 1 and 1440." exit 1 fi # #---------------------------------------------------------------------------- # ...verify that ORACLE_HOME environment variable is set... #---------------------------------------------------------------------------- if [[ "${ORACLE_HOME}" = "" ]] then echo "ORACLE_HOME not set; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...verify that ORACLE_SID environment variable is set... #---------------------------------------------------------------------------- if [[ "${ORACLE_SID}" = "" ]] then echo "ORACLE_SID not set; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...verify that ORACLE_HOME environment variable appears to be set correctly #---------------------------------------------------------------------------- if [ ! -d ${ORACLE_HOME}/bin ] then echo "Directory \"${ORACLE_HOME}/bin\" not found; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # ...verify that the "sqlplus" executable is present and executable... #---------------------------------------------------------------------------- if [ ! -x ${ORACLE_HOME}/bin/sqlplus ] then echo "Cannot execute \"${ORACLE_HOME}/bin/sqlplus\"; aborting..." exit 1 fi if [ ! -x ${ORACLE_HOME}/bin/sqlldr ] then echo "Cannot execute \"${ORACLE_HOME}/bin/sqlldr\"; aborting..." exit 1 fi # #---------------------------------------------------------------------------- # Create the table to hold results from "iostat". Protect the Oracle # account's password by keeping it away from the UNIX command-line... #---------------------------------------------------------------------------- _SpoolFile=/tmp/iostat_$$.lst ${ORACLE_HOME}/bin/sqlplus -s /nolog <<__EOF__ > /dev/null 2> /dev/null whenever oserror exit failure whenever sqlerror exit failure set echo on feedback on timing on spool ${_SpoolFile} connect ${_UnPwd} whenever sqlerror continue drop table temp_iostat; whenever sqlerror exit failure create table temp_iostat ( timestamp date not null , device varchar2(100) not null , nbr_reads_per_sec number not null , nbr_writes_per_sec number not null , kb_reads_per_sec number not null , kb_writes_per_sec number not null , waits number not null , actv number not null , wsvc_t number not null , asvc_t number not null , pct_waits number not null , pct_busy number not null ) storage (initial 1 next 1M pctincrease 0) nologging; exit success __EOF__ if (( $? != 0 )) then echo "SQL*Plus failed: please check \"${_SpoolFile}\" for errors..." exit 1 else rm -f ${_SpoolFile} fi # #---------------------------------------------------------------------------- # Create a SQL*Loader control file with which to load the "iostat" results... #---------------------------------------------------------------------------- _CtlFile=/tmp/iostat_$$.tmp echo "load data" > ${_CtlFile} echo "infile *" >> ${_CtlFile} echo "into table temp_iostat" >> ${_CtlFile} echo "fields terminated by '~'" >> ${_CtlFile} echo "( nbr_reads_per_sec" >> ${_CtlFile} echo ", nbr_writes_per_sec" >> ${_CtlFile} echo ", kb_reads_per_sec" >> ${_CtlFile} echo ", kb_writes_per_sec" >> ${_CtlFile} echo ", waits" >> ${_CtlFile} echo ", actv" >> ${_CtlFile} echo ", wsvc_t" >> ${_CtlFile} echo ", asvc_t" >> ${_CtlFile} echo ", pct_waits" >> ${_CtlFile} echo ", pct_busy" >> ${_CtlFile} echo ", device" >> ${_CtlFile} echo ", timestamp date(10) \"RRMMDDHH24MI\"" >> ${_CtlFile} echo ") begindata" >> ${_CtlFile} # #---------------------------------------------------------------------------- # Call "iostat -xnP" for each minute. Strip off the header labels as well # as the standard "initial reading" for statistics from system-startup to # the current point-in-time. Save only statistics for the current 60-second # interval... #---------------------------------------------------------------------------- integer i=0 while (( ${i} < ${_Mins} )) do # _TStamp=`date '+%y%m%d%H%M'` # iostat -xnP 60 2 2> /dev/null | \ awk '{if($2=="device"||$11=="device")x=x+1; \ else if(x>=4)\ print $1"~"$2"~"$3"~"$4"~"$5"~"$6"~"\ $7"~"$8"~"$9"~"$10"~"$11"~'${_TStamp}'"}' \ >> ${_CtlFile} # integer i=${i}+1 # done # #---------------------------------------------------------------------------- # Create a (secured) SQL*Loader parameter file to use during loading... #---------------------------------------------------------------------------- _ParFile=/tmp/iostat_$$.par _LogFile=/tmp/iostat_$$.log _BadFile=/tmp/iostat_$$.bad _DscFile=/tmp/iostat_$$.dsc echo "userid=${_UnPwd}" > ${_ParFile} chmod 600 ${_ParFile} echo "control=${_CtlFile}" >> ${_ParFile} echo "log=${_LogFile}" >> ${_ParFile} echo "bad=${_BadFile}" >> ${_ParFile} echo "discard=${_DscFile}" >> ${_ParFile} echo "direct=true" >> ${_ParFile} echo "errors=0" >> ${_ParFile} echo "discardmax=0" >> ${_ParFile} # #---------------------------------------------------------------------------- # Load the "iostat" data into Oracle... #---------------------------------------------------------------------------- ${ORACLE_HOME}/bin/sqlldr parfile=${_ParFile} > /dev/null 2> /dev/null rm -f ${_ParFile} # remove parameter file to protect password # #---------------------------------------------------------------------------- # Check the SQL*Loader "log file" for any evidence of problems during # loading... #---------------------------------------------------------------------------- if grep "ORA-[0-9][0-9][0-9][0-9][0-9]" ${_LogFile} > /dev/null 2>&1 then echo "SQL*Loader failed: please check \"${_LogFile}\" for errors..." exit 1 fi if grep "^ 0 Rows successfully loaded." ${_LogFile} > /dev/null 2>&1 then echo "SQL*Loader failed: please check \"${_LogFile}\" for errors..." exit 1 fi if ! grep "^ 0 Rows not loaded due to data" ${_LogFile} > /dev/null 2>&1 then echo "SQL*Loader failed: please check \"${_LogFile}\" for errors..." exit 1 fi if ! grep "^ 0 Rows not loaded because all WHEN" ${_LogFile} > /dev/null 2>&1 then echo "SQL*Loader failed: please check \"${_LogFile}\" for errors..." exit 1 fi if ! grep "^ 0 Rows not loaded because all fields" ${_LogFile} >/dev/null 2>&1 then echo "SQL*Loader failed: please check \"${_LogFile}\" for errors..." exit 1 fi # #---------------------------------------------------------------------------- # If completed successfully, then clean up any temporary files... #---------------------------------------------------------------------------- rm -f ${_CtlFile} ${_LogFile} ${_BadFile} ${_DscFile} # #---------------------------------------------------------------------------- # Report on the summarized "iostat" information... #---------------------------------------------------------------------------- ${ORACLE_HOME}/bin/sqlplus -s <<__EOF__ ${_UnPwd} whenever oserror exit failure whenever sqlerror exit failure set echo off feedback off trimspool on trimout on tab off verify off set serveroutput on size 1000000 spool iostat_${ORACLE_SID} declare cursor get_stats is select device, sum(nbr_reads_per_sec+nbr_writes_per_sec)*60 io, (sum(kb_reads_per_sec+kb_writes_per_sec)*60)/1024 mb from temp_iostat group by device having sum(nbr_reads_per_sec+nbr_writes_per_sec)*60 > 0 order by 3 desc; -- v_io_min number; v_mb_min number; v_running_mb number := 0; begin select sum(nbr_reads_per_sec+nbr_writes_per_sec)*60, (sum(kb_reads_per_sec+kb_writes_per_sec)*60)/1024 into v_io_min, v_mb_min from temp_iostat; -- for x in get_stats loop if get_stats%rowcount = 1 then dbms_output.put_line(rpad('Device',17) || lpad('#IOs',11) || ' ' || lpad('%IOs',6) || ' ' || lpad('#Mb',14) || ' ' || lpad('%Mb',6) || ' ' || lpad('Running Mb',14) || ' ' || ' %Run'); dbms_output.put_line(rpad('-',16,'-') || ' ' || rpad('-',11,'-') || ' ' || rpad('-',6,'-') || ' ' || rpad('-',14,'-') || ' ' || rpad('-',6,'-') || ' ' || rpad('-',14,'-') || ' ' || '------'); end if; -- v_running_mb := v_running_mb + x.mb; -- dbms_output.put_line(rpad(substr(x.device,1,16),16) || to_char(x.io,'999,999,990') || to_char((x.io/v_io_min)*100,'990.00') || to_char(x.mb,'999,999,990.00') || to_char((x.mb/v_mb_min)*100,'990.00') || to_char(v_running_mb,'999,999,990.00') || to_char((v_running_mb/v_mb_min)*100,'990.00')); end loop; end; / exit success __EOF__ # #---------------------------------------------------------------------------- # Completed #---------------------------------------------------------------------------- exit 0