Re: not able to turn on sql_trace for an instance

Fabrizio Napolitano wrote:
> Niall thank you very much.
>
> The application has a log and we are using it at the moment
> the problem is that is not very clear; for example it gives two
> different times :
> OPEN DATA 41 min
> READ DATA 1h 06 min
>
> at this point it is not clear if the Open data is the time to retrieve
> the first row or an internal time of the application spent in who knows
> what.
>
> I think it is the time to retrieve the first row but to be sure of
> that I would like to trace the run.
>
> I can capture the SQL on the flight with TOAD and then executed it in a
> traced session, but you can imagine that if I choose a small cube then
> it is not
> so easy to capture it and if I take a long running one then it is a bit
> boring.
>
> If I am able to generate all the traces can I aggregate them in a big
> one file before to
> use tkprof?
>
> Last question to understan if I got it right this time:
> I change my init setting sql_trace TRUE
> shotdown/start the db
> and then I can use the Alter System set sql_trace on...
> or I have to set the par in init.ora shotdown/start the db
> then change it again and shotdown/start the db??
>

You set it in the init.ora, bounce and then its active until you unset it and bounce again.

Personally, I prefer a database logon trigger which checks for the specific user and then sets event 10046 with:

- level 4 (for bind values) or
- level 8 (for wait events) or
- level 12 (both bind and waits) for the session.

HTH Holger Received on Fri Aug 22 2003 - 11:27:56 CDT