Home » RDBMS Server » Performance Tuning » Problem with output file using TKPROF - Oracle 9i Release 2
Problem with output file using TKPROF - Oracle 9i Release 2 [message #185312] Tue, 01 August 2006 03:03 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
I am using "Oracle9i Enterprise Edition Release 9.2.0.7.0"
I wanted to find out slow running queries in the database.

I generated trace file with following
alter session set events '10046 trace name context forever,level 12';

and used TKProf as following
tkprof d:\pratap\tkprof_trcs\dev13_ora_29776.trc d:\pratap\tkprof_trcs\text_29776.txt sort=prsela

However i could not found the user defined SQL statements in the output file generated.
(File attached for Reference)

Then i changed the sort parameter value for "prscpu"
tkprof d:\pratap\tkprof_trcs\dev13_ora_29776.trc d:\pratap\tkprof_trcs\text_29776_01.txt sort=prscpu

still could not found User Defined (and expected slow running SQLs)

Please suggest.

Thanks and Regards,
Pratap

Note : Output files are attached for reference
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185333 is a reply to message #185312] Tue, 01 August 2006 04:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Are you sure that you traced the correct session?
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185336 is a reply to message #185333] Tue, 01 August 2006 04:43 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
Thanks for Quick Reply
Yes, i traced the correct session but unable to see many queries.
Does the output file has limit on the number of SQLs displayed in it?

Thanks and Regards,
Pratap
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185535 is a reply to message #185312] Wed, 02 August 2006 03:22 Go to previous messageGo to next message
orafaqer
Messages: 48
Registered: July 2006
Member
Attach raw trace file.
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185753 is a reply to message #185333] Thu, 03 August 2006 07:32 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
i could see the trace files after 2 hours in Udump.(for all the 2-3 sessions i was trying)
Could not know what happened.

Also i want to know the following
I start tracing sessions using
exec sys.dbms_system.set_sql_trace_in_session(51,2963,true);

Is trace file generated immediately after this command?

Also when shall i call the following then?
exec sys.dbms_system.set_sql_trace_in_session(51,2963,false);

Please reply and help.

Thanks and Regards,
Pratap
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185810 is a reply to message #185753] Thu, 03 August 2006 13:28 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Yes, the trace file would be generated immediately and you need the stop the tracing once you are done with the queries (or whenever you want to).
Re: Problem with output file using TKPROF - Oracle 9i Release 2 [message #185812 is a reply to message #185753] Thu, 03 August 2006 13:30 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once upon a time, when one in SQL*Plus issued

SQL> ALTER SESSION SET SQL_TRACE = TRUE

and executed query he wanted to trace, it was necessary to EXIT CURRENT SQL*Plus SESSION in order to be able to apply TKPROF against the trace file.

[EDIT]

Hm, yes, or issue another ALTER SESSION SET SQL_TRACE = FALSE.

[Updated on: Thu, 03 August 2006 13:31]

Report message to a moderator

Previous Topic: Find Session with high cpu usage
Next Topic: High parse count over db_link from 9i to 9i?
Goto Forum:
  


Current Time: Thu Dec 08 14:19:44 CST 2016

Total time taken to generate the page: 0.18534 seconds