Enabling the sql_trace parameter
I have a database server with Oracle9i on a Solaris machine. I want to enable the sql_trace parameter in init.ora...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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: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);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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments