SQL trace and TKPROF in SQLPLUS

September 8, 2009



>>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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers