SQL trace and TKPROF in SQLPLUS

>>Script Language and Platform: Oracle
Purpose: To read raw session trace file with option to format trace file with TKPROF and read it in SQLPLUS Session.

Author: Neaman Ahmed



#!/bin/sh
#################################################################
#################################################################
## Copyright (c)2004 Kalson Systems ##
## Author :Neaman Ahmed ##
## ScriptName:trace ##
## Purpose: To read raw session trace file with option to ##
## format trace file with TKPROF and read it in SQLPLUS Session##
## You must set TRACE_DIR and ORACLE_SID in ##
## this script Put trace in < $HOME/bin > make it executable ##
## and run it from sqlplus session ##
## Example: SQL> ! trace ##
## Critics, Comment and suggestion are welcome nomiak@yahoo.com##
## Note:You Must Run this script from your sqlplus session ##
#################################################################
#################################################################
clear
n1=`ps |grep sqlplus|awk ‘{print $1}’ `
n2=`ps -ef|grep $n1|grep oracle”$ORACLE_SID”|awk ‘{print $2}’`
TRACE_DIR=/u02/app/oracle/admin/$ORACLE_SID/udump

echo ” 1.Do you want to view raw trace file”
echo ” 2.Do you want to format trace file and view it”
read choice
if [ “$choice” = “1” ];
then cat $TRACE_DIR/”$ORACLE_SID”_ora_$n2.trc|more
else
if [ “$choice” = “2” ];
then
tkprof $TRACE_DIR/”$ORACLE_SID”_ora_$n2.trc $TRACE_DIR/trace_session_$n2
clear
echo “Your TKPROF formated file name is $TRACE_DIR/trace_session_$n2.prf ”
echo ” Do you want to read it now y/n ”
read ans
if [ “$ans” = “y” ];
then
cat $TRACE_DIR/trace_session_$n2.prf|more
else
if [ “$ans” = “n” ];
then
echo “Happy Hunting Perfomance Problems”
fi
fi
fi
fi



Disclaimer:
We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles