Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: query tuning using tkprof80

Re: query tuning using tkprof80

From: Daniel Ebright <debright_at_mortgageflex.com>
Date: Fri, 16 Jul 1999 15:48:36 -0400
Message-ID: <378F8C94.E4A665C9@mortgageflex.com>


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 set
USER_DUMP_DEST = newdir)
MAX_DUMP_FILE_SIZE = set maximum size of the files. Default is 5120

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US