where does my script spend the most of time?
Hi all,
I'd like to know where does my script spend the most of time.
Please find code below.
I'm newbie and would like to know how do I start script debugging?
Any suggestion is appreciated!
Code:$ less /QA/u01/oracle/admin/plsdw/plsdw_scripts/nag_pls_exec_sql_ftp.ksh #!/bin/ksh #************************************************************************************************** # Script: nag_pls_exec_sql_ftp.ksh # # # Description: Script to run sql commands in the Oracle database, and then transfer output files. # #************************************************************************************************** export ver=v1.14 set -f # do not substitute filenames for * (in SQL queries) export ORAENV_ASK=NO export PATH=/usr/bin:/usr/local/bin:/bin HOSTNAME=`hostname|tr [:lower:] [:upper:]` MAIN_SCRIPT_LONG=$0 MAIN_SCRIPT_SHORT=`basename $MAIN_SCRIPT_LONG|tr [:lower:] [:upper:]` CFG_FILE=`dirname $0`/$1 . $CFG_FILE # source cfg file . $MAIN_ORA_PROFILE # source main oracle profile (Oracle settings) TS=$(date +'%Y%m%d%H%M%S') LOCK_FILE=$LOG_DIR/$PREFIX.lck MAIN_LOG=$LOG_DIR/${PREFIX}_main.log DELTA_LOG=$LOG_DIR/${PREFIX}_${TS}.log WARN_LOG=$LOG_DIR/${PREFIX}_WARN.log SQL_SCRIPT=$LOG_DIR/${PREFIX}_${TS}_sql.sql SQL_LOG=$LOG_DIR/${PREFIX}_${TS}_sql.log SFTP_SCRIPT=$LOG_DIR/${PREFIX}_${TS}_sftp.sh SFTP_LOG=$LOG_DIR/${PREFIX}_${TS}_sftp.log CFG_ESTIM=$LOG_DIR/${PREFIX}_estim.cfg SQL_ESTIM=$LOG_DIR/${PREFIX}_estim_sql.sql ESTIM_FILTER=$1 notific="SUCCESS!" sep1="=======================================================================================================" ora_avg_time_query() { print "set head off" > $SQL_ESTIM print "conn $DBCONN_AUDIT" >> $SQL_ESTIM print " select 'ESTIM='||TO_CHAR(extract(hour from TO_TIMESTAMP(tb.Average,'HH24:MI:SS'))*60*60+ " >> $SQL_ESTIM print " extract(minute from TO_TIMESTAMP(tb.Average,'HH24:MI:SS'))*60+ " >> $SQL_ESTIM print " extract(second from TO_TIMESTAMP(tb.Average,'HH24:MI:SS'))) as av_time " >> $SQL_ESTIM print " from PLS_LOG_DETAILS_MVIEW tb " >> $SQL_ESTIM print " where tb.cron_job='$ESTIM_FILTER'; " >> $SQL_ESTIM print "exit" >> $SQL_ESTIM sqlplus /nolog @$SQL_ESTIM | grep ESTIM > $CFG_ESTIM err1=$? # save exit code # if [ $err1 != 0 ]; then # finish "SQL Average Time Failure!" # fi . $CFG_ESTIM } rm_files() { [ -e $DELTA_LOG ] && rm $DELTA_LOG [ -e $SQL_SCRIPT ] && rm $SQL_SCRIPT [ -e $SQL_LOG ] && rm $SQL_LOG #[ -e $SQL_QUERY_OUT ] && rm $SQL_QUERY_OUT [ -e $SFTP_SCRIPT ] && rm $SFTP_SCRIPT [ -e $SFTP_LOG ] && rm $SFTP_LOG } init() { if [ -e $LOCK_FILE ] && [ "$IS_ONLY_ONE_INSTANCE" == "1" ]; then if [ "$NOTIF_EMAIL" == "1" ]; then st1="Script is already running. '$LOCK_FILE' found." echo "$st1"|mailx -s "FAILURE! $DESC.$(hostname)." "$EMAILS" if [ "$ADMIN_EMAILS" != "" ]; then echo "$st1"|mailx -s "FAILURE! (admin email) $DESC.$(hostname)." "$ADMIN_EMAILS" fi exit 1 fi fi touch $LOCK_FILE rm_files d1=$(date +"%D %H:%M:%S") d1second=$SECONDS print $sep1 |tee -a $DELTA_LOG print $(date) |tee -a $DELTA_LOG print $sep1 |tee -a $DELTA_LOG # explicitly set flag if email must be sent in case of error only if [ "$EMAIL_ON_ERR_ONLY" != "1" ]; then EMAIL_ON_ERR_ONLY=0 fi # mode is 'file' by default (all output files are described in the cfg file) [ "$SQL_MODE" == "" ] && SQL_MODE=file log_id=$(sqlplus -s /nolog <<-EOF|awk 'NR==2 {print $1}' set head off set feed off set pagesize 0 conn $DBCONN_AUDIT select pls_audit.get_id_log from dual; EOF) sqlplus /nolog <<-EOF conn $DBCONN_AUDIT exec pls_audit.log_start($log_id,\ '$HOSTNAME.$MAIN_SCRIPT_SHORT.START',\ 'STARTED AT: $d1, $MAIN_SCRIPT_LONG $CFG_FILE, ORACLE_SID=$ORACLE_SID'); EOF err1=0 } get_attachment() { case $SQL_MODE in file) for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do attach=$(eval print "\$SFTP_FILE$i"|awk '{print $1}') a1=$(eval print "\$SFTP_FILE$i"|awk '{print $3}') if [ "$a1" == "1" ]; then print "Attachment: '$attach'"|tee -a $DELTA_LOG return fi done attach="" print "No attachments."|tee -a $DELTA_LOG ;; query) attach=$SQL_QUERY_OUT ls -la $attach a2=`grep -i Cell $SQL_QUERY_OUT | wc -l` if [ $a2 != 0 ]; then print "Attachment: '$attach'"|tee -a $DELTA_LOG else print "No attachments."|tee -a $DELTA_LOG attach="" fi ;; esac } finish() { notif_msg="$1 $(date +"%D %H:%M:%S").$(hostname).$DESC." print "$notif_msg" |tee -a $DELTA_LOG d2=$(date +"%D %H:%M:%S") d2second=$SECONDS printf "\nScript ($ver) worked:\n" |tee -a $DELTA_LOG print "begin: $d1" |tee -a $DELTA_LOG print "end : $d2" |tee -a $DELTA_LOG print $sep1 |tee -a $DELTA_LOG cat $CFG_FILE >> $MAIN_LOG # SAVE CFG FILE TO THE MAIN LOG cat $DELTA_LOG >> $MAIN_LOG # SAVE SESSION LOG TO THE MAIN LOG if [ "$NOTIF_EMAIL" == "1" ]; then if [[ "$EMAIL_ON_ERR_ONLY" == "1" && "$err1" != "0" ]] || [ "$EMAIL_ON_ERR_ONLY" == "0" ]; then get_attachment if [ "$attach" == "" ]; then if [ "$SQL_MODE" != "query" ]; then cat $DELTA_LOG|mailx -s "$1 $DESC.$(hostname)." "$EMAILS" if [ "$ADMIN_EMAILS" != "" ]; then cat $CFG_FILE $DELTA_LOG| mailx -s "$1 (admin email) $DESC.$(hostname)." "$ADMIN_EMAILS" fi fi else (cat $DELTA_LOG;uuencode $attach $(basename $attach))|mailx -s "$1 $DESC.$(hostname)." "$EMAILS" if [ "$ADMIN_EMAILS" != "" ]; then (cat $CFG_FILE $DELTA_LOG;uuencode $attach $(basename $attach))| mailx -s "$1 (admin email) $DESC.$(hostname)." "$ADMIN_EMAILS" fi fi fi time_diff=$(($d2second - $d1second)) if [ ! -z $ESTIM ]; then SQL_PRC_GAIN=$(($time_diff*100/$ESTIM-100)) if [[ $SQL_PRC_GAIN -gt $SQL_WARNING_TRESHOLD_PRC ]]; then print "\nActual time: $time_diff seconds \nAverage for 7 Days: $ESTIM seconds \n$SQL_PRC_GAIN% gain \n" > $WARN_LOG cat $CFG_FILE $DELTA_LOG >> $WARN_LOG mailx -s "SQL Warning! (admin email) $DESC.$(hostname)." $ADMIN_EMAILS < $WARN_LOG rm -f $WARN_LOG fi fi fi rm_files sqlplus /nolog <<-EOF conn $DBCONN_AUDIT exec pls_audit.log_end( $log_id,\ '$HOSTNAME.$MAIN_SCRIPT_SHORT.$1',\ 'COMPLETED AT: $d2, $MAIN_SCRIPT_LONG $CFG_FILE, ORACLE_SID=$ORACLE_SID'); EOF rm $LOCK_FILE exit $err1 } generate_xml() { f1=_tmp1 f2=_tmp2 cat $SQL_QUERY_OUT |sed -e 's/^"//g;s/"$//g;s/","/|/g'|tr '|' '\t' > $f1 [ -e $SQL_QUERY_OUT ] && rm $SQL_QUERY_OUT # prepare XML header while read LINE; do print $LINE >> $SQL_QUERY_OUT done <<EOF <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>a</Author> <LastAuthor>a</LastAuthor> <Created>2013-08-18T18:36:57Z</Created> <Company>a</Company> <Version>11.9999</Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>11385</WindowHeight> <WindowWidth>15180</WindowWidth> <WindowTopX>360</WindowTopX> <WindowTopY>90</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Arial Cyr"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s21"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial Cyr" ss:Bold="1"/> </Style> <Style ss:ID="s22"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table> <Column ss:Width="50"/> <Column ss:Width="50"/> <Column ss:Width="50"/> EOF print "<Row>" >> $SQL_QUERY_OUT for i in `awk 'NR==1' $f1` do print ' <Cell ss:StyleID="s21"><Data ss:Type="String">'$i'</Data></Cell>' >> $SQL_QUERY_OUT done print "</Row>" >> $SQL_QUERY_OUT awk 'NR>1' $f1|while read LINE; do print "<Row>" >> $SQL_QUERY_OUT print "$LINE"|awk -F\t '{for (i=1;i<=NF;i++){print $i}}' > $f2 while read i; do k=$(print "$i"|sed 's/&/&/g') # replace ampersand print ' <Cell ss:StyleID="s22"><Data ss:Type="String">'$k'</Data></Cell>' >> $SQL_QUERY_OUT done < $f2 print "</Row>" >> $SQL_QUERY_OUT done # add XML footer while read LINE; do print $LINE >> $SQL_QUERY_OUT done <<EOF </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <PageSetup> <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996" x:Right="0.78740157499999996" x:Top="0.984251969"/> </PageSetup> <Print> <ValidPrinterInfo/> <HorizontalResolution>200</HorizontalResolution> <VerticalResolution>200</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>1</ActiveRow> <ActiveCol>2</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook> EOF [ -e $f1 ] && rm $f1 [ -e $f2 ] && rm $f2 } ora_process_query() { [ $err1 -ne 0 ] && return print "Start Oracle ..."|tee -a $DELTA_LOG print "Query: " |tee -a $DELTA_LOG print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" |tee -a $DELTA_LOG print $SQL_QUERY |tee -a $DELTA_LOG print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" |tee -a $DELTA_LOG print "conn $DBCONN" > $SQL_SCRIPT print "set feed off" >> $SQL_SCRIPT print "set pagesize 0" >> $SQL_SCRIPT print 'set colsep ""","""' >> $SQL_SCRIPT print "set linesize 5000" >> $SQL_SCRIPT print "set pagesize 5000" >> $SQL_SCRIPT print "set trimspool on" >> $SQL_SCRIPT print "spool $SQL_QUERY_OUT" >> $SQL_SCRIPT print "$SQL_QUERY" >> $SQL_SCRIPT print "exit" >> $SQL_SCRIPT sqlplus -s /nolog @$SQL_SCRIPT >> $SQL_LOG err1=$? # save exit code err2=`grep -i ora- $SQL_LOG|wc -l|awk '{print $1}'` # if SQL_LOG has any ORA- errors if [[ $err1 != 0 || $err2 != 0 ]]; then finish "SQL Failure!" fi sed -e 's/[ ]*["][,]["]/","/g;s/^[ ]*/"/g;s/$/"/g' $SQL_QUERY_OUT|sed -e '3d'|sed -e '1d' > /tmp/_f1 mv /tmp/_f1 $SQL_QUERY_OUT [ "$SQL_QUERY_TYPE" == "XML" ] && generate_xml } ora_work() { [ $err1 -ne 0 ] && return print "Start Oracle ..." |tee -a $DELTA_LOG print "conn $DBCONN" > $SQL_SCRIPT # put all commands to Oracle database in ordered way for i in `awk '/^SQL_FILE/ {print substr($0,9,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do eval print "\$SQL_FILE$i" >> $SQL_SCRIPT done sqlplus /nolog @$SQL_SCRIPT >> $SQL_LOG err1=$? # save exit code print "SQL LOG:" |tee -a $DELTA_LOG cat $SQL_LOG |tee -a $DELTA_LOG # save sql log to the main log err2=`grep -i ora- $SQL_LOG|wc -l|awk '{print $1}'` # if SQL_LOG has any ORA- errors if [[ $err1 != 0 || $err2 != 0 ]]; then finish "SQL Failure!" fi } save_local() { [ $err1 -ne 0 ] && return [ "$HIST_DIR" == "" ] && return print |tee -a $DELTA_LOG print -n "Save files to the local dir ..." |tee -a $DELTA_LOG for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do generated_file=$(eval print "put \$SFTP_FILE$i"|awk '{print $2}') # fullname of the generated file fn=$(basename $generated_file) # shortname of the generated file history_file=${fn%%.*}_$TS.${fn##*.} # add timestamp between name and extension cp -p $generated_file $HIST_DIR/$history_file # copying done print "done." |tee -a $DELTA_LOG } sftp_chk() { [ $ERR_IF_RMT_FILES_EXIST -eq 0 ] && return print |tee -a $DELTA_LOG print -n "Checking if output files exist on the dst host..."|tee -a $DELTA_LOG for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do f1=$(eval print "\$SFTP_FILE$i"|awk '{print $2}') res=$(ssh $SFTP_DST_HOST "ls -la $f1" 2>/dev/null|wc -l|awk '{print $1}') if [ $res -eq 1 ]; then print -n "\nOutput file '$f1' exists on the remote host '$SFTP_DST_HOST'"|tee -a $DELTA_LOG err1=1 fi sleep 5 # to prevent net flood done if [ $err1 != 0 ]; then print |tee -a $DELTA_LOG print "Warning! Some output files exist on the dst host!"|tee -a $DELTA_LOG notific="WARNING!" err1=0 else print "done."|tee -a $DELTA_LOG fi } sftp_work() { [ $err1 -ne 0 ] && return [ "$SFTP_DST_HOST" == "" ] && return sftp_chk # check out if files exist on dst host print -n > $SFTP_SCRIPT # put all commands to sftp utility in ordered way for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do eval print "\$SFTP_FILE$i"|awk '{print "put "$1" "$2}' >> $SFTP_SCRIPT done print "bye" >> $SFTP_SCRIPT print |tee -a $DELTA_LOG print "Transfer file to the destination host and dir ..." |tee -a $DELTA_LOG sftp -b $SFTP_SCRIPT $SFTP_DST_HOST >> $SFTP_LOG 2>>$DELTA_LOG err1=$? # save exit code print |tee -a $DELTA_LOG print "SFTP LOG:" |tee -a $DELTA_LOG cat $SFTP_LOG |tee -a $DELTA_LOG # save sftp log to the main log if [ $err1 != 0 ]; then finish "SFTP Failure!" fi } ftp_chk() { [ $ERR_IF_RMT_FILES_EXIST -eq 0 ] && return print |tee -a $DELTA_LOG print -n "Checking if output files exist on the dst host..."|tee -a $DELTA_LOG for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do file1=$(eval print "\$SFTP_FILE$i"|awk '{print $2}') dir1=$(dirname $file1) print "ls $dir1" > $SFTP_SCRIPT print "bye" >> $SFTP_SCRIPT cat $SFTP_SCRIPT|ftp $FTP_DST_HOST > $SFTP_LOG 2>&1 res=$(grep $file1 $SFTP_LOG|wc -l|awk '{print $1}') if [ $res -eq 1 ]; then print -n "\nOutput file '$file1' exists on the remote host '$FTP_DST_HOST'"|tee -a $DELTA_LOG err1=1 fi sleep 5 # to prevent net flood done if [ $err1 != 0 ]; then print |tee -a $DELTA_LOG print "Warning! Some output files exist on the dst env!"|tee -a $DELTA_LOG notific="WARNING!" err1=0 else print "done."|tee -a $DELTA_LOG fi } ftp_work() { [ $err1 -ne 0 ] && return [ "$FTP_DST_HOST" == "" ] && return ftp_chk # check out if files exist on dst host if [ "$FTP_TYPESET" != "" ]; then print "$FTP_TYPESET" > $SFTP_SCRIPT fi # put all commands to ftp utility in ordered way for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'` do eval print "\$SFTP_FILE$i"|awk '{print "put "$1" "$2}' >> $SFTP_SCRIPT done print "bye" >> $SFTP_SCRIPT print |tee -a $DELTA_LOG print "Transfer file to the destination host and dir ..." |tee -a $DELTA_LOG cat $SFTP_SCRIPT|ftp -v $FTP_DST_HOST > $SFTP_LOG 2>&1 err1=$? # save exit code print |tee -a $DELTA_LOG print "SFTP LOG:" |tee -a $DELTA_LOG cat $SFTP_LOG |tee -a $DELTA_LOG # save ftp log to the main log # Analyze ftp log if it contains any errors err2=$(cat $SFTP_LOG|awk "/No such file or directory/||/Login incorrect/"|wc -l|awk '{print $1}') if [[ $err1 != 0 || $err2 != 0 ]]; then finish "FTP Failure!" fi } # Main script init if [ ! -z $SQL_WARNING_TRESHOLD_PRC ]; then ora_avg_time_query fi case $SQL_MODE in query) ora_process_query ;; file) ora_work save_local case $TRANSFER in SFTP) sftp_work;; FTP) ftp_work;; esac ;; *) err1=1 finish "SQL_MODE is not defined! Must be file or query." ;; esac finish $notific (END)
Adv Reply
Bookmarks