Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query tuning using tkprof80
I just did this last week for my supervisor concerning a specific app on
our SUN Solaris 7 box running Oracle 8.0.5.
First you must set the following parameters in INITXXX.ORA file.
TIMED_STATISTICS = Enables and disables the collection of timed
statistics TRUE or FALSE
USER_DUMP_DEST = specify fully the destination for the trace file
location. Can be changed using ALTER SESSION
command.(alter system setUSER_DUMP_DEST = newdir)
I chose to connect to to the system with the app and then using SVRMGR30 get the following info from V$SESSION (SID, SERIAL#, OSUSER) and then alter the session to perform auto tracing on that session/user by doing the following.
At the SVRMGR prompt type:
EXECUTE dbms_session.set_sql_trace_in_session (SID,SERIAL#,TRUE);
Then begin utilizing your application. I had to keep a careful eye on
accumulation of the trace files.
Be sure, if you app is a big one and generates alot of SQL commands, to
make sure you have plenty
of space available for the trace files. Once they started accumulating, I
started renaming them to more
appropriate names and also kept those names with the TKPROF80 utility.
Hope this helps...Dan Ebright
Franz Mueller wrote:
> Hi,
>
> I would like to trace the queries from a given app. How can I make a
> trace file that I could later analyze with "tkprof80"?
> If I try the "dbms_system.set_sql_trace_in_session(sid,serial,true)" I
> get a "identifier must be declared" error message. What is wrong?
>
> Thank you
> Franz
Received on Fri Jul 16 1999 - 14:48:36 CDT