TKprof Question

Hi all,
Sometimes I'm able to generate the text file with the sql and stats but sometimes it produces nothing. What I'm I doing wrong here.

Thanks

-I set the parameters here as DBA

SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,true);

PL/SQL procedure successfully completed.

----I executed a sql from a client session
----I turn off tracing
SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,false);

PL/SQL procedure successfully completed.


Now Tkprofing...


tkprof tttt09_ora_7412.trc trace.txt explain=system/xxxxx table=sys.plan_table sys=no waits=yes

open output file

cat trace.txt

----results below. Why is the sql qnd stats not captured?

TKPROF: Release 9.2.0.5.0 - Production on Wed Mar 25 12:49:25 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: tttt09_ora_7412.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: tttt09_ora_7412.trc
Trace file compatibility: 9.02.00
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.
511 lines in trace file.

Are you sure the query you want to trace is being executed in the session you are tracing? Does not seem it is.
:p

no sql is executed...
include one parameter in tkprof i.e record=filename to see non-recursive sql executed by the session..

Quote:

---

Originally Posted by LKBrwn_DBA

Are you sure the query you want to trace is being executed in the session you are tracing? Does not seem it is.
:p

---

It should......

this is what I do

get the sid /serial# of the connected user from v$session

as DBA set parameters

SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,true); ---params from above

PL/SQL procedure successfully completed.

executed a sql from the session/sid/serial# above

then turn off tracing

SQL> exec sys.dbms_system.set_sql_trace_in_session(67,21397,false);

PL/SQL procedure successfully completed.

Is there something missing? when I then do the TKprof?

Are you sure it's the only session for that user?

Are you selecting the correct trace file?

From what utility/application is the user executing his queries?

Make user logoff/logon before/after trace.
:confused:

Is this session coming to the database through a job? I have noticed that sometimes when this happened to me, it turned out to be the j001 process. Check the OS process by joining v$session to v$process view.