Re: Tracing and tkprof

mthyagaraj_at_neural.com wrote in message <78t2cf$2bo$1_at_nnrp1.dejanews.com>:
>I want to know how to turn on tracing at the database level, and how to to
>'tkprof' the trace file. This is to diagnose a maxing out of processor time
>on our Oracle 7 server.

Hi,

To collect Oracle SQL trace files database wide, insert the following two lines in your init.ora

            sql_trace      = true
            user_dump_dest = <some directory name>

and restart your database.

Unfortunately, this doesn't generate a single trace file to be analysed, but one per user session. There may be many hundreds or thousands. You can concatenate these files together and then run tkprof on the concatenated file, eg.

        tkprof xxxx.trc xxxx.prf sort="execpu,fchcpu" [ explain=/ ]

( Where xxxx.trc is the concatenation of your trace files )

This will format the statistics and sort them so that the most expensive SQL command ( measured by the sum of execution and fetch cpu time ) is presented first. Unfortunately, tkprof can't handle large trace files, so it isn't as simple as that. I have a script which I run nightly to batch up trace files into batches of 100 and run tkprof on each batch. I then use other awk scripts to analyse the tkprof output and produce a summary of all commands executed by the database during the day. If you want them, the scripts are at:

        http://home.clara.net/dwotton/dba/tkprof_utils.htm

Dave.
--
Remove "nospam" from my address to reply by email Received on Sat Jan 30 1999 - 15:24:26 CST