Q

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

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close