sql_trace = truein the init.ora file. But when I use this command
show parameter sql_traceat the SQL prompt, it displays that sql_trace is false. I have also tried to use the command like
alter session set sql_trace = truebut still it is display it false. How should I initialize this parameter? Actually when I issue any SQL command it is generated in the .trc file in the udump directory, but with query plan it is not there. Can I enable this parameter through the DBA admin tool? I have tried from there also, but when I restarted the database, the same problem occurs.
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:oracle9iadminORCL92udumpDelete 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);A DBA may enable tracing for another user's session by using the following statement:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);The sid (Session ID) and serial# can be obtained from the v$session view. This package is owned by the SYS user and therefore the executor must be SYS or be granted the EXECUTE privilege by SYS user.
This was first published in July 2004