Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Formatting the Trace File Oracle Tips by Burleson

Once you have generated the trace files, the next step is to format the trace file. This utilizes the TKPROF utility to format and make the trace file readable.

You can find the location of your input-tracefile with the following SQL*Plus command:

select
   name,
   value
from
   v$parameter
where
   name = 'user_dump_dest'
;

Here is the output that show that the trace files are written to /u01/app/oracle/admin/udump:

NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
user_dump_dest
/u01/app/oracle/admin/prodsys1/udump

The TKPROF utility has a wealth of command line options. You can type the TKPROF command from the UNIX prompt with no command line arguments to get a usage and parameter listing. As you can see, there are dozens of formatting options that can be applied to a trace file.

root>tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAIN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

The first step in running TKPROF is to go to the user_dump_dest directory and find our trace file:

sting*prodsid1-/u01/app/oracle/admin/prodsid1/udump
>ls -alt|head
total 128
-rw-r-----   1 oracle   dba         5083 Apr  1 11:16 prodsid1_ora_7330.trc

Now, we can issue the TKPROF command from the UNIX prompt, directing the listing to a file called mytrace.lst. Note that we also use the sort options, sorting first by number of disk reads during execute followed by the number of reads during fetch.

root>tkprof testb1_ora_7330.trc mytrace.lst \
     explain=applsys/manager sort=’(exedsk, fchdsk)’

TKPROF: Release 8.1.6.1.0 - Production on Sun Apr 1 11:19:18 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Next, we can display the mytrace.lst file and see the details about the execution of our SQL statement. Let’s take a look at the contents of a standard TKPROF report.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.