Re: trace file reuse???

Jerry,

      I don't have an issue with the naming convention/mechanism to create the trace file.

The problem is that there is no way to refresh a trace file in the same client session. I want to be able to run trace on a series of commands, move the trace file to a new name, make changes to the database, and rerun trace on the same commands. Now, I would have 2 trace files that I can compare and see if my database tuning changes helped. If you just turn trace on and off all the results accumulate in the single trace file making it difficult to determine whether the tuning was helpful. The only way to get two separate trace files is to reconnect the client which is a real pain. I would of thought this feature I'm looking for would of been a general that most dba would like.
One of the problem with the trace file is it gets large very quickly. It would be nice if you had the ability to empty and reuse it during the same user session

Recreating the trace files on the second sql_trace run does not work nor copying an empty file to the trace file.

I tried copying an empty file to the trace file but that does not work either.
It looks like oracle holds some file descriptor that points to where it is currently in the file. If I run trace on mySQL, tthen copy an empty file to the
trace file, following by rerunning trace on mySQL. The trace file gets filled
with nulls values for the first run of mySQL.

For example:
If I sql trace 'mySql' and it generates a trc file 'myDB_ora_12345.trc' which is
100K.
Then, I copy an empty file to myDB_ora_12345.trc', and rerun the same trace on
mySql I get a trace file that is 200K with the first 100k all nulls.

"Jerry Gitomer" <jgitomer_at_erols.com> wrote in message news:20000525.5360900_at_noname.nodomain.nowhere...

I think it has to do with Oracle's trace file naming mechanism. If I remember correctly it appends the serial to the string <SID>_ and then tacks the.trc extension on.

Why do you have to reconnect to the client? Just open multiple windows. Have one pointing to the directory with the trace files and one running SQL. That way you can stay connected.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 5/24/00, 2:10:23 PM, "Gary Knopp" <gknopp_at_ascend.com> wrote regarding trace file reuse???:

> Hello,
> Does anyone know whether its possible to reuse a trace file
> in a client session? I've been unable to make this work.
 

> For example...
> execute dbms_system.set_sql_trace_in_session(10,200,True)
> ...
> ... run sql commands here
> ...
> execute dbms_system.set_sql_trace_in_session(10,200,False)

> This generates a file named 'myDB_ora_12345.trc' . If I
> rename the file to 'Trial1_hold.trc' and rerun the commands again
> the trace file 'myDB_ora_12345.trc' is not created again. I
> don't understand why the trace package doesn't handle this
condition.

> It's a real pain having to reconnect the client to test various
 query
> scenarios.
Received on Thu May 25 2000 - 00:00:00 CDT