OraFAQ Forum: Performance Tuning » Problem with output file using TKPROF

Home » RDBMS Server » Performance Tuning » Problem with output file using TKPROF - Oracle 9i Release 2

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with output file using TKPROF - Oracle 9i Release 2 [message #185312]

Tue, 01 August 2006 03:03

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

Attachment: output_files.zip
(Size: 7.46KB, Downloaded 1371 times)

Report message to a moderator

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

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Are you sure that you traced the correct session?

Report message to a moderator

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

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

Report message to a moderator

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

orafaqer
Messages: 48
Registered: July 2006

Member

Attach raw trace file.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21808
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 01 21:36:08 CDT 2024