Subject:            RUNNING SQL_TRACE FROM ORACLE TOOLS
Last Revision Date: 16 January   1995
Author:MBOOKMAN



-------------------------------------------------------------------------------

                  RUNNING SQL_TRACE FROM ORACLE TOOLS

Note: This bulletin is also posted under SQL*Forms(#106354.083) and
      SQL*ReportWriter#(106372.887)

This bulletin documents how to turn on SQL_TRACE from the various
Oracle Tools.  Documentation on the tracing facility and TKPROF can be
found in Chapter 7 of the ORACLE RDBMS Performance Tuning Guide (V6) and
Appendix B of the ORACLE7 Server Application Developer's Guide (V7).

=============
SQL*Forms 2.3
=============
Method 1:
=========
  Create a front-end dummy form which will turn on SQL_TRACE and call
  the form you want to run the trace against.  This will be very generic,
  so you could use it anytime you want to issue trace against a form.

  1 - Create a dummy table in the database with one column and one row.

      Note that if you don't put in one row, Step 1 of the KEY-STARTUP
      we will define will return "Query caused no records to be retrieved."
      and the rest of the trigger will not be executed.

  2 - Create a dummy form with a database block based on this dummy table.

  3 - Create a KEY-STARTUP trigger with the following steps:

      Step 1: #exemacro exeqry;
      Step 2: select 'x' into :dummy_field from dual
      Step 3: #exemacro commit; call formname;

      Where "dummy_field" is the field in your dummy table/block,
      and "formname" is the name of the form you want to run SQL_TRACE
      against.

  4 - Create a POST-UPDATE on the dummy block with the following step

      alter session set sql_trace true

  What happens when you run this dummy form is the KEY-STARTUP fires, updates
  a record and starts the commit process.  This in turn fires the post-update
  trigger.  We need to use a post-update trigger since the alter session
  statement must be in a "commit-time" trigger.  Then the trace is on, and you
  can call your "real" form that you want to run trace against.

Method 2:
=========
  See RTSS bulletin 99265.341 for how to do this in a USER EXIT.

===================
SQL*Forms Version 3
===================
  From page 26-5 of the SQL*Forms Designer's Reference:

Method 1:
========
  From the Designer, go to Options.
  Select the "Statisics" option.
  Run your Form from the Designer.

Method 2:
========
  Use the -s switch on the runform command line.

======================
Oracle Forms Version 4
======================
From page 1-27 of the Oracle Forms Reference Manual, Volume 1:

Method 1:
========
  In the Designer, go to Edit/Preferences.
  Select the "Statistics" option.
  Run your Form from the Designer.

Method 2:
=========
  From page 1-18 of the Oracle Forms Reference Manual, Volume 1:
  Set "statistics=YES" on the F40RUN command line.

============================
SQL*Reportwriter Version 1.1
============================
Method 1:
=========
  In the Designer, Open your report and create a new field.
  Name   : sql_trace
  Source : &SQL alter session set sql_trace true
  Group  : Report
  Width  : 1
  Skip   : X

  Run the report as usual.

Method 2:
=========
  Due to bug 190946, which caused some queries to be missing from
  the trace file, one may need to do the following:
  Create a dummy report with just one field as above.
  Have a dummy query like : select 1 from dual;
  Make the report to write to a null file so as not to have any output.
  Save the report.

  Then, every time you want to run sql_trace for a report:
    1 - Go into the Designer.
    2 - Execute the dummy report above.
    3 - Open and run the report that you wish to generate the trace for.

==========================
ORACLE Reports Version 2.0
==========================
Method 1:
=========
  From page 609 of the Oracle Reports Reference Manual:

  1 - Open the report for which you want the performance data.
  2 - Create a report-level formula column named SQL_TRACE that has
      the following settings:

  Formula:  SRW.DO_SQL('ALTER SESSION SET SQL_TRACE TRUE');

  3 - Run the report.

Method 2:
=========
  Due to bug 182852 which can occasionally cause a trace without explain
  plan information, you can also:

  1 - Open the report for which you want the performance data.
  2 - Create a Before Form trigger that has:

  SRW.DO_SQL('ALTER SESSION SET SQL_TRACE TRUE');

  3 - Run the report.

========
SQL*Plus
========
  1 - Connect to SQL*Plus
  2 - Issue : alter session set sql_trace true;
  3 - do your sql statements
  4 - Issue : alter session set sql_trace false;