Enabling the sql_trace parameter

initialization file. I write it as

 
sql_trace = true 

show parameter sql_trace

alter session set sql_trace = true 

The SQL Trace facility is not used much anymore and I recommend it only if you need a detailed dump, i.e. when the SQL returns invalid data.

You do a detailed dump using the TKPROF utility and setting sql_trace=true is a prerequisite, but there are many other steps and you will also need special scripts to analyze the dump!

Dave Moore's book Oracle Utilities has complete instructions, and I highly recommend it.

Anyway, here are some of the set-up directions for TKPROF (from Dave's Oracle Utilities book):

Step 1: Check the Environment

Before tracing can be enabled, the environment must first be configured by performing the following steps:

• Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The command to enable timed statistics is:

  ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
  

• Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora.

  SQL> select value
  from v$parameter
  where name = 'user_dump_dest';
  
  VALUE
  ---------------------------------
  C:oracle9iadminORCL92udump
  

  Delete unwanted trace files before starting a new trace to free up the disk space.

Step 2: Turn Tracing On

The next step in the process is to enable tracing. Tracing can be defined at the session level:

ALTER SESSION SET SQL_TRACE = TRUE;

DBMS_SESSION.SET_SQL_TRACE(TRUE);

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);