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 = true 
in the init.ora file. But when I use this command
show parameter sql_trace
at the SQL prompt, it displays that sql_trace is false. I have also tried to use the command like
alter session set sql_trace = true 
but 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.

    Requires Free Membership to View

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.


This was first published in July 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: