TKprof Question

[Tuma]

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.

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

[mahajanakhil198]

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

[Tuma]

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

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?

[LKBrwn_DBA]

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.

[bhatia333]

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.