Database Tuning‎ > ‎

Using SQL tracing in the Oracle Tools

posted Jul 27, 2011, 11:52 AM by Sachchida Ojha
This article documents the use of SQL tracing in the various Oracle tools.
This is not to be confused with SQL*Net tracing which shows all database
operations sent through the SQL*Net connections.

In all cases, the trace files will be deposited in the location specified by
the init.ora parameter USER_DUMP_DEST.  It should be noted that this is given a
default value under UNIX, which can be seen from inside SQL*DBA.  This is
not set by default in a PC/Netware environment, so must be explicitly set.
Otherwise, no trace files will be produced.  Under VMS, this parameter may be
overwritten by setting the ORA_DUMP logical.  In all cases, ensure that the 
write permissions are set correctly for the destination directory.

Also, for more readable output, the tkprof utility can be invoked on the trace
file.  The use of tkprof is documented in Appendix B of the Application
Developers Guide for Oracle 7 and in Section 7 of the Performance Tuning Guide
for Oracle 6.

The trace facility may be turned on at the database level, by inserting the
following line in the init.ora file:

                           sql_trace = true

This will trace all database activity whilst the database is running.  Only use
this method if all transactions need to be monitored, since a large number of
trace files can be generated in this way.  In order to take effect, the 
parameter needs to be added to the init.ora file and the database restarted. 
Remember to remove the parameter and restart the instance, once the required 
trace information has been collected.


   Tracing can be set by issuing the command: 
                    SQL> alter session set sql_trace true;
   Once the transactions requiring tracing have executed, tracing can be turned
   off by issuing the command:
                    SQL> alter session set sql_trace false;


   In Oracle 6, trace may be invoked within SQL*Plus as above, prior to running
   the PL/SQL script.  In Oracle 7 the same method may be used, or the stored
   procedure dbms_session.set_sql_trace() may be invoked with the syntax:
   This may be inserted at the first line of the PL/SQL script.  If this package
   or procedure cannot be found, then it may need to be created by the dba 
   running the dbmsutil.sql script as the user sys.  This dbms_session package 
   is documented on page A-2 of the Version 7 Application Developers Guide.
   It is advisable to turn off tracing.

   From within SQL*Plus:
                SQL> alter session set sql_trace false;
   From within PL/SQL after the transactions requiring tracing have executed:             
   Either of these statements will ensure that no more trace output than is 
   necessary is generated.

Forms 2.3

   Tracing can be set by creating a user defined trigger called trace_on.  For
   example, containing only the line:

                 alter session set sql_trace true
   This trigger can be called from a key-startup trigger as follows:
                 #exemacro exetrg trace_on;
   Once this trigger has fired, then all further database actions will be
   traced and recorded in the trace file.  This can be useful if errors are 
   occurring saving the form to the database, as tracing can be invoked
   inside the form followed by the save/load/generate operation that results 
   in error(s) and all database actions will still be stored in the trace file.

Forms 3

   There is a statistics option for a runform session, which can either be
   invoked from the options screen of the designer or by using the -s flag
   when calling runform from the operating system prompt.  This statistics
   option is documented on page 23-5 of the SQL*Forms 3 Reference Manual.
   If it becomes necessary to trace the actions when a form is saved/loaded
   to/from the database, then a form must be run from the designer with
   statistics on and then the database action performed.  If the session is 
   running against an Oracle 7 database then an alternative to using the 
   statistics option is to use:
   This is to be used as a line inside a trigger or PL/SQL procedure, usually 
   the pre-form trigger.

Forms 4

   There is a statistic option which is documented on page 1-18 of the Forms 4 
   Reference Manual Volume 1 (packaged procedure dbms_session).  The parameter
   'set_sql_trace()' may also be invoked inside a when-new-form-instance 
   trigger, similarly to the method described in the previous section.

Graphics 2

   Create the following PL/SQL program unit:
                  procedure set_trace is
   Next, define the open display trigger to invoke this procedure.  If an open
   display trigger has already been defined, then just adding the following line
   will invoke the trace facility:
   However, note that much more sophisticated debugging methods are available
   within the PL/SQL debugger.

Reportwriter 1.1

   Tracing can be invoked from inside a report by creating a field with the
   following settings:
                      Name   -> sql_trace
                      Source -> &SQL alter session set sql_trace true
                      Group  -> REPORT
                      Width  -> 40
   Ensure that the field label is deleted, the skip field is set on, and that
   there is no reference to this field on the text screens.
   Running the report will enable the tracing, which will continue until the
   reports session is ended.
   However, there is a known BUG 190946, which states that setting alter session
   set sql_trace true inside a report produces a trace file that only has a 
   trace of the alter session statement.  This bug was first noted in version and is fixed in version  This basically means that a 
   report cannot set a trace onto itself, and hence the following workaround is 
   First, create and execute a dummy trace report and then execute the real 
   report.  This means that tracing can only be done from within the designer, 
   as both reports cannot be run with a single runrep command (and therefore in 
   the same session).
   To create the trace report follow these steps:

      1. Create a report with 1 field and 1 query as in:
         Field: Name   -> sql_trace
                Source -> &SQL alter session set sql_trace true
                Group  -> Report
                Width  -> 40
         Query: Anything, for example, select 'a' from dual.
         It may be preferable to ensure that this report writes to a null file 
         to ensure that there is no output generated.

      2. Each time a report is to be traced, follow these instructions:
         a. Start the designer
         b. Execute this trace report
         c. Execute the report to be traced.

   This method has the advantage that the report being traced does not have to 
   be altered in any way.
   Bear in mind that once the sql_trace has been enabled, it will remain enabled
   until the end of the reports session.  This means that in the event of 
   multiple reports beginning debug it is advisable to exit the reports designer
   after each report has been traced, thus resulting in a separate trace file 
   for each report.
Reports 2

   There are 2 methods for setting sql_trace here.  Firstly, use in the
   Before-Report trigger:
   Secondly, create a function column at report level, with a formula of:
            srw.do_sql('alter session set sql_trace true') 
   and a return type of char.  Ensure that this column is not displayed on the 
   report layout.

   Trace can be invoked by including the following line in the source program:
                 exec sql alter session set sql_trace true;
   Tracing can also be turned off by using:
                 exec sql alter session set sql_trace false;