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;


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>