Re: Applications Tracing

Yes you can do.

Try
sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE|FALSE);

to generate Trace files for the session (gets collected in a directory speciied by USER_DUMP_DEST) and then use TKPROF to format the trace file to get details of the Session including Explain plan,CPU,Time Usage etc..

If you want to trace multiple sessions from the Same client machine, you may want to execute the above statement within a FOR Loop like

begin
for sess_rec in ( Select sid,serial# from V$session where machine like ('&machine'))
loop
sys.dbms_system.set_sql_trace_in_session(sess_rec.sid,sess_rec.serial#,TRUE);

end loop;
end;
/

Tkprof the trace files and then

End the Tracing by Changing TRUE to FALSE and repeat the loop..

Hope this helps..

Thiru

"Robert M. Kopf" wrote:

> Is there a way to trace all SQL that is being sent between an Oracle
> Applications client and the database server? I have tried to use the
> Trace functionality on the Apps Help menu with little success.
> Specifically, I am trying to trace the queries executed when a report
> is run. I have copied the SQL out of the report and run it in SQL*Plus
> with the same parameters as wehn run from Applications. However, I
> receive no rows of data.
>
> Any assistance is greatly appreciated.
>
> --
> Robert Kopf
> Map Mobile Communications, Inc.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Aug 03 1999 - 13:18:56 CDT