I have a database server with Oracle9i on a Solaris machine. I want to enable the sql_trace parameter in init.ora initialization file. I write it as
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.