TKPROF trace files

[st2000]

Hello :

I am trying to look at some stuff in the alert_SID.log and over the weekend, I saw some entries with ORA-60 and dead lock error was detected and a trace file number mentioned alongwith in the udump.. When I went to udump and tkprof the file, I see no information about deadlock, but standard information..

oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> more 92778.out

TKPROF: Release 8.1.7.1.0 - Production on Mon Aug 19 09:02:14 2002

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

Trace file: ././ora_92778_odbmsprd.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: ././ora_92778_odbmsprd.trc
Trace file compatibility: 8.00.04
Sort options: default

1 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
12787 lines in trace file.

I started looking at more trace files and I see nothing but only info like this.. How else can I decode these tracefiles.?

I use tkprof tracefile trace.out explain=uid/pwd

Thanks, ST2000

[Edited by st2000 on 08-19-2002 at 09:41 AM]

[marist89]

http://metalink.oracle.com/metalink/...T&p_id=62365.1

[st2000]

Thanks Jeff for the nice article..

What I meant to ask was that when I TKPROF the file mentioned in the alert.log for the deadlock, why does it not contain the stuff I am looking for..

See the following..

From my alert.log file---
Thread 1 advanced to log sequence 51205
Current log# 3 seq# 51205 mem# 0: /u09/dbs/ODBMSPRD/redo03.log
Sat Aug 17 05:41:38 2002
ORA-000060: Deadlock detected. More info in file /home/oracle/admin/ODBMSPRD/udump/ora_92778_odbmsprd.trc.
Sat Aug 17 05:48:03 2002

In my udump directory
oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> ls -l ora_92778*
-rw-r--r-- 1 oracle dba 591272 Aug 17 05:41 ora_92778_odbmsprd.trc


generating a tkprof output file
oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> tkprof ora_92778_odbmsprd.trc 92778.out explain=cotprod/odbms

TKPROF: Release 8.1.7.1.0 - Production on Mon Aug 19 11:23:19 2002

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


oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> ls -l 92778*.*
-rw-r--r-- 1 oracle dba 1135 Aug 19 11:23 92778.out

Contents of the out file..
oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_> more 92778.out

TKPROF: Release 8.1.7.1.0 - Production on Mon Aug 19 11:23:19 2002

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

Trace file: ././ora_92778_odbmsprd.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: ././ora_92778_odbmsprd.trc
Trace file compatibility: 8.00.04
Sort options: default

1 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
12787 lines in trace file.


oracle@CG_JDA1:/home/oracle/admin/ODBMSPRD/udump_>

I definitely believe and I was expecting more info in the tracefile that I have now, such as some deadlock query statements and table sinvolved and so on.. rather than nothing.. Where I am wrong and what am I missing.. When I TKPROFed, should I include more parameters or something to see more information..

Thanks, ST2000

[marist89]

Why do you think you need to tkprof the file? The article clearly explains how to get the row & object name in question. Also, the SQL Statement that failed is in the trace file.

[st2000]

Makes sense now.. Jeff..

I thought I needed to TKPROF any trace file and was thus doing that since I am used to tracing SQL statements and using TKPROF..

Thanks again, ST2000

[pando]

tkprof is to trace trace files generated sql_trace not any trace files, there isnt any tool which is distributed externally that does that