SQL trace and TKPROF in SQLPLUS

Tuesday Sep 8th 2009 by DatabaseJournal.com Staff
Share:

Purpose: To read raw session trace file with option to format trace file with TKPROF and read it in SQLPLUS Session.



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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved