-
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.
Looking for the greatest evil in the world? Look in the mirror.
-
Invisible client?
Are you sure the query you want to trace is being executed in the session you are tracing? Does not seem it is.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
no sql is executed...
include one parameter in tkprof i.e record=filename to see non-recursive sql executed by the session..
lucky
-
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.
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?
Looking for the greatest evil in the world? Look in the mirror.
-
Session/Trace file?
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.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
When in doubt ...go to the basics!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|