Subject: Setting SQL Trace in the Oracle Tools
Author: RBEARD
Modified: 31 Oct 95 11:45:33
Using SQL tracing in the Oracle Tools
=====================================
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, whereas 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.
SQL*Plus
========
Tracing can be set by simply 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;
PL/SQL
======
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 following
syntax:
dbms_session.set_sql_trace(true);
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 either:
SQL> alter session set sql_trace false;
within SQL*Plus, or:
dbms_session.set_sql_trace(false);
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 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:
dbms_session.set_sql_trace(true);
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. The packaged procedure dbms_session.
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
begin
dbms_session.set_sql_trace(true);
end;
next, define the open display trigger to invoke this procedure. If an open
display trigger has already been defined, then just adding the line:
dbms_session.set_sql_trace(true);
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, that 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, number 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 1.1.14.9.1 and is fixed in version 1.1.14.12. This basically means
that a report cannot set a trace onto itself, and hence the following
workaround is suggested:
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 begin debugged 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:
dbms_session.set_sql_trace()
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.
Precompilers
============
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;