Mar 29, 2015

How to do SQL Tracing

 

  1. Login to sqlplus using appropriate user.
  2. Execute following commands

Alter session set timed_statistics = true;

--(Enable Timed Statistics – This parameter enables the collection of certain vital --statistics such as CPU execution time, wait events, and elapsed times.)


Alter session set user_dump_dest = <some file path on server> ;

Alter session set sql_trace = true;

    3. Run the query or Procedure


Alter session set sql_trace = false;

Exit;

4. Go to the specified file path. Check for latest *.trc file.
5. Run the following command

TKPROF trace_filename.trc out_put_filename.txt sys=no explain=<username>/<password>