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.
SQL*Plus
========
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;
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 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.
From within SQL*Plus:
SQL> alter session set sql_trace false;
From within PL/SQL after the transactions requiring tracing have executed:
dbms_session.set_sql_trace(false);
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:
dbms_session.set_sql_trace(true);
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
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 following line
will invoke the trace facility:
dbms_session.set_sql_trace(true);
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
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 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:
dbms_session.set_sql_trace()
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; |
Database Tuning >